If Formula ??

R

Rugby Al

I have a spreadsheet with values in a single row 16 columns wide, I want to
give the cells below a position ie 1st 2nd 3rd... dependant on the values

2.1 5.1 3.2 1.6
3rd 1st 2nd 4th

As the values can change, the position need to automatically change as well..
 
C

CLR

Assuming your values are in A1:D1,
Put this formula in A2 and copy over to D2...........

=RANK(A1,$A$1:$D$1,0)

or for the reverse,

=RANK(A1,$A$1:$D$1,1)

Vaya con Dios,
Chuck, CABGx3
 
R

Rugby Al

That works fine, Thanks

If the cells are not next to each other but in the same row how can you
modify the formula
 
C

CLR

Say the cells instead of A1:D1 were A1,C1,E1, and G1.........I would make a
Non-contiguious RangeName of those cells, called
"NCRange".........(highlight the cells while holding down the Ctrl button,
then Insert > Name > Define > and type NCRange in the upper window > OK)

then make the formula in A2
=RANK(A1,NCRange)

Then just copy and paste the formula to C2, E2, and G2

Vaya con Dios,
Chuck, CABGx3
 
R

Rugby Al

Thanks
Works great

CLR said:
Say the cells instead of A1:D1 were A1,C1,E1, and G1.........I would make a
Non-contiguious RangeName of those cells, called
"NCRange".........(highlight the cells while holding down the Ctrl button,
then Insert > Name > Define > and type NCRange in the upper window > OK)

then make the formula in A2
=RANK(A1,NCRange)

Then just copy and paste the formula to C2, E2, and G2

Vaya con Dios,
Chuck, CABGx3
Click to expand...
 
Top