Ranking Cell Values

[Guest]

I'm stuck. I would like to assign a ranking to the values in a row for each cell in that row on one worksheet (example: 50, 25, 75, 30) to a row of cells on another worksheet ( solution: 3,1,4,2) and can't figure out the formula. Any suggestions?

 

[Vasant Nanavati]

If your numbers are in say C5:F5, select a cell on the other sheet and type
in:

=RANK(Sheet1!C$5,Sheet1!$C$5:$F$5,1)

Copy across 4 columns.

--

Vasant

I'm stuck. I would like to assign a ranking to the values in a row for

each cell in that row on one worksheet (example: 50, 25, 75, 30) to a row
of cells on another worksheet ( solution: 3,1,4,2) and can't figure out the
formula. Any suggestions?

 

[Guest]

Thanks! I will give it a try. You have saved me many hours of searching!

 

[Guest]

It works! Thanks!

 

[Guest]

Vasant....one more question if you are still out there...

How do I do the same ranking operation, but want to skip every other cell on Sheet 1 ( C5, E5, G5, I5, etc? I have tried several variations on your formula and haven't been able to make it work. Thanks.

 

[Vasant Nanavati]

Something like (untested):

=RANK(Sheet1!C$5,(Sheet1!$C$5,Sheet1!$E$5,Sheet1!$G$5,Sheet1!$I$5),1)

should work.

--

Vasant

Vasant....one more question if you are still out there....

How do I do the same ranking operation, but want to skip every other cell

on Sheet 1 ( C5, E5, G5, I5, etc? I have tried several variations on your
formula and haven't been able to make it work. Thanks.

 

[Guest]

That works great. Thanks
One more question if I may...if one of the cell values on sheet1 is 0 (zero), is there a way to remove it from the rankings on sheet2 and insert the words "No Value"?