Excel Trouble

Litejedi

Hello, H/A. I am trying to finish a lab report, and I have a set of data points that are being analyzed. I want to "lock in" a value for H2 in the following cell, but want the others to increment normally.

=B2/((A3-A2)*H2)

I.e. I want to be able to pull down the cells and keep H2 as H2. I remember there is a way to do this, but I don't remember what it is. Does anyone know?

The Cat

put $'s before the H and the 2.

Blake T

The other more elegant solution if it's a common repeating value is click on the H2 cell and on the top left hand of the menus click in H2 and rename it to something (eg DOLLARS) then replace where you would have H2 with the word dollars in your formula.

Litejedi

The Cat wrote: »

put $'s before the H and the 2.

Thank you so much! I thought it was something simple.

It doesn't seem to work, is it because I'm using the click/drag autofill feature?

Cryogen

Whats going wrong? I dummied up a spreadsheet with your formula and used autofill, seems to be working ok.

Of course i've just put in simple testing numbers and i dont exactly know how and what direction you are filling, but the formula itself should still hold its integrity

Litejedi

I'm filling downward,
=B2/((A3-A2)*$J2)
=B3/((A4-A3)*$J3)
=B4/((A5-A4)*$J4)
=B5/((A6-A5)*$J5)

I want J2 to stay as J2 for the entire length down.

Cryogen

Oh, easy. You want $J$2

Otherwise it only maintains the J

Zonkytonkman

actually, he only needs J$2

the dollar sign locks up the "variable" immediately after.

Litejedi

Ahh, I understand, I wasn't doing it correctly.

Cryogen

True, i guess its just habit as i copy formulas sideways a lot as well, so i tend to need to absolute both values

blanknogo

For ease of use, just highlight the variable you want to lock and press F4 to scroll through the possible choices.

Zonkytonkman

yeah, it's usually safer to do both variables, but sometimes you need it to increment sideways.

I learned some usefull new tricks in excel today, like inserting text next to a result so that units can be in the same cell as the number, and text string searching.

woot.