Coping rank for whole dataset in excel

hols - Apr 9, 2010 at 05:23 AM
 hols - Apr 9, 2010 at 06:36 AM
i want to work out the ranks for my whole data set, if i type in the formula for the first line then drag it for the rest instead of working out that rank for that entry in the whole dataset it moves the begining of the data set down with the cell ref.

For example, i type =rank(A1;A1:A166,0)

then when i copy it to the other cells instead of getting: =rank(A2;A1:A166,0) etc

i get =rank(A2;A2,A167,0)

how do i get it to work out the of each entry over the dataset without it changing the dataset each time too?



1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 9, 2010 at 06:25 AM
To keep address of a cell constant you need to use $

$A$1 is absolute to A1 always

$A1 is absolute column A but row will change

A$1 is absolute row 1, but column would change

Thank you!

you saved my life:)