Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I rank numbers based on other numbers? | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
How do I sum YTD totals based on monthly totals | Excel Discussion (Misc queries) | |||
Can I assign a rank based on a cells' value | Excel Worksheet Functions | |||
Rank() based on category | Excel Worksheet Functions |