Rank based on Totals
 

I have a spreadsheet that tallies numbers per month for different
categories. I tally the totals on the 13th column. What I want to do
is create a function that then ranks these scores from 1 to 1000
(arbitrary) based upon the score (low to high). For example, the
category whose total is closest to 0 should be ranked 1 and so on.

Can someone recommend a function?

Thanks


--
B. Baumgartner
------------------------------------------------------------------------
B. Baumgartner's Profile: http://www.excelforum.com/member.php...o&userid=23107
View this thread: http://www.excelforum.com/showthread...hreadid=396992

Try this for a start. The =rank(array) formula will give you a straight
ranking of the array -- i.e. the highest value equals 1, the second
highest equals 2, and so on. I've created an "inverse rank" formula by
subtracting the rank from the number of items being ranked and then
adding one. I've also given you an example of a "scoring system" that
lets you set a minimum score and maximum score and the number of digits
right of the decimal point that you'd like to allow for the score, then
score the values, spreading the score proportionately between the min
and max.

See what you think.


+-------------------------------------------------------------------+
|Filename: For_B. Baumgartner.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3705 |
+-------------------------------------------------------------------+

--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=396992

B. Baumgartner Wrote:
I have a spreadsheet that tallies numbers per month for different
categories. I tally the totals on the 13th column. What I want to do
is create a function that then ranks these scores from 1 to 1000
(arbitrary) based upon the score (low to high). For example, the
category whose total is closest to 0 should be ranked 1 and so on.

Can someone recommend a function?

Thanks

Try this in column 14

=RANK(m1,m:m,1)


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=396992