Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Ranking
Hi all,
Trying to figure out the "Rank" function and not doing well. I have a column of data formated as % and would like to see what the rank each one is without sorting them assending. If some one can just explain the use of the rank function I would appreciate it. Unless of course there is a better way! xl2k 130.64% 2 167.39% 10 187.91% 14 139.48% 6 130.86% 3 126.51% 1 203.13% 15 139.04% 5 214.37% 16 181.59% 11 185.17% 13 182.76% 12 257.97% 17 165.81% 8 133.17% 4 166.64% 9 142.08% 7 I did this the "HARD" way and still not real confident they are right. Need a formula. Mike R. |
#2
|
|||
|
|||
The rank function takes 3 arguments. The fist is the number you wish to rank,
the second is the range to compare that number to and the third determines if you want the comparison done ascending or descending. So assuming your data is in the range A3:A19 you would enter the following formula in cell B3 and then copy it down. =RANK(A3,$A$3:$A$19,1) The reference to A3 is relative so it will change as you copy down. In each case this is the number you are ranking. The range is absolute and stays fixed in each case. The final 1 states that you want to order ascending i.e. the lowest value will have a rank of 1. For the highest value having a rank of one you would enter 0 here. Hope this helps Rowan "Mike R" wrote: Hi all, Trying to figure out the "Rank" function and not doing well. I have a column of data formated as % and would like to see what the rank each one is without sorting them assending. If some one can just explain the use of the rank function I would appreciate it. Unless of course there is a better way! xl2k 130.64% 2 167.39% 10 187.91% 14 139.48% 6 130.86% 3 126.51% 1 203.13% 15 139.04% 5 214.37% 16 181.59% 11 185.17% 13 182.76% 12 257.97% 17 165.81% 8 133.17% 4 166.64% 9 142.08% 7 I did this the "HARD" way and still not real confident they are right. Need a formula. Mike R. |
#3
|
|||
|
|||
Rowan
Thanks you for the post... It was the absolute reference that did it!!!! Thanks for the help and the contribution to my continuing education. Mike R. "Rowan" wrote: The rank function takes 3 arguments. The fist is the number you wish to rank, the second is the range to compare that number to and the third determines if you want the comparison done ascending or descending. So assuming your data is in the range A3:A19 you would enter the following formula in cell B3 and then copy it down. =RANK(A3,$A$3:$A$19,1) The reference to A3 is relative so it will change as you copy down. In each case this is the number you are ranking. The range is absolute and stays fixed in each case. The final 1 states that you want to order ascending i.e. the lowest value will have a rank of 1. For the highest value having a rank of one you would enter 0 here. Hope this helps Rowan "Mike R" wrote: Hi all, Trying to figure out the "Rank" function and not doing well. I have a column of data formated as % and would like to see what the rank each one is without sorting them assending. If some one can just explain the use of the rank function I would appreciate it. Unless of course there is a better way! xl2k 130.64% 2 167.39% 10 187.91% 14 139.48% 6 130.86% 3 126.51% 1 203.13% 15 139.04% 5 214.37% 16 181.59% 11 185.17% 13 182.76% 12 257.97% 17 165.81% 8 133.17% 4 166.64% 9 142.08% 7 I did this the "HARD" way and still not real confident they are right. Need a formula. Mike R. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking | Excel Discussion (Misc queries) | |||
Order ranking. Any suggestions? | Excel Worksheet Functions | |||
Ranking cells largest to smallest | Excel Discussion (Misc queries) | |||
ranking an row of values | Excel Worksheet Functions | |||
Problem with ranking numbers | Excel Worksheet Functions |