Ranking Cell Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Jim said:
I'm stuck. I would like to assign a ranking to the values in a row for
Click to expand...
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....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.
 
Something like (untested):

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

should work.

--

Vasant

Jim said:
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
Click to expand...
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.
 
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"?
 
Back
Top