If Formula ??

[Guest]

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..

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.RANK.html

 

[CLR]

Assuming your values are in A11,
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

 

[Guest]

That works fine, Thanks

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

 

[CLR]

Say the cells instead of A11 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

 

[Guest]

Thanks
Works great

Say the cells instead of A11 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