Coping rank for whole dataset in excel

[Solved/Closed]
Report
-
 hols -
Hi
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?

thanks

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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

=rank(A1;A$1:A$166,0)
Thank you!

you saved my life:)