locking ranges in functions while re-sorting data

[Matthew Dyer]

This is quite possibly the most frustraiting thing I've found in
Excel... Some functions easily re-adjust themselves to the new
parameters when the data is re-sorted. I've found that when the
functions require a range of data (a2:a8 for example), when the data
is re-sorted, the function tries to re-adjust not only the acutal cell
coordinate in the formula, but also the range coordinates as well. Is
there any way to get the range to stay the same but the single cell to
adjust?

Ex formula -

=IF(B2<>"N/A", ((RANK(B2,B2:B8)) -1)/(COUNTIF(B2:B8, ">=0")-1), "N/A")

When I re-sort the data, then the "B2:B8" ranges automatically try to
adjust themselves, which I DO NOT want. I DO want the "B2" coordinate
to re-adjust itself to the proper new row it's in.

 

[Niek Otten]

B2 is relative, $B$2 is absolute (Doesn't change when put somewhere else)
Mixed variations are $B2 and B$2, self-explanatory, I think
If you enter or edit a formula, when the cursor is on or next to the address
in the fomula bar, F4 toggles the 4 styles of relative/absolute adsressing