[PGH]

Hi,

I have a list of 40 numbers in Excel (2002), and what I want Excel to do in an adjacent column is give each of the 40 original values a rank number (eg. put a 1 next to the higest number out of the 40, and a 40 next to the lowest - or maybe not a 40 if there are duplicates...)

Anyway I am using the formula =Rank(number, array, order), which I hope is right for what I want to do, however I am having problems when I drag this forumla down the column as the wrong parts of it automatically change.

To start with I want to find the rank of A1 out of A1:A40, and I set the order to 0, so I have this:

=Rank(A1,A1:A40,0) and it works fine and tells me how this number is ranked out of A1-A40

however I want to find the rank of all the numbers A1-A40 out of A1:A40, but when I drag the forumla down the column it goes wrong.

I want the forumla to do this

=Rank(A1,A1:A40,0)
=Rank(A2,A1:A40,0)
=Rank(A3,A1:A40,0)
=Rank(A4,A1:A40,0) etc.

but not just the number, but also the array changes. In reality it does this:

=Rank(A1,A1:A40,0)
=Rank(A2,A2:A41,0)
=Rank(A3,A3:A42,0)
=Rank(A4,A4:A43,0)

Is it possible to set it so only the first part of the forumla changes, and the array from which it is taking the rank stays the same - so all the numbers can be ranked out of all the numbers, and not some strage moving rank is acheived?

Thanks so much

Edited by PGH, 17 February 2006 - 12:12 PM.

[Advertisements]

i think if ou put a $ before the array...like this

=Rank(A1,$A1:$A40,0)

it should do it...

[dsenette]

i think if ou put a $ before the array...like this

=Rank(A1,$A1:$A40,0)

it should do it...

[dsenette]

nope...just tried it

[dsenette]

got it

=Rank(A1,$A$1:$A$40,0)
whatever you put a $ before will stay the same in the formula...so if you just want the collumn to stay the same...put the $ before the a...if you want the fixed cell you have to put it between both

[PGH]

That is great, thank you very much!