How to rank a series of totals in Excel?

Question

I have been asked to create a spreadsheet template for my supervisor. Basically, what he wants to be able to do is take a bunch of totals and rank them (first, second, third, etc.) In other words, if the following data appears in a row:

Joe Jane David Mike Sara Diane
100 97 63 27 75 84

...and he has a ranking chart as follows:

1.
2.
3.
etc.

... he wants the column next to each number to auto-populate with the person who ranks #1, #2, etc.

If it helps, what he is doing is taking 9 billers and entering their daily totals, summing the weekly totals at the bottom, and finding who is #1, #2, etc., for total claims billed in a week.

I have discovered the RANK function, but have discovered its limitation: I can't do what he wants to do if two people have the same total. In other words, if both Sara and Diane bill 75 claims, it won't return a person in fourth place or denote a tie for third.

Any suggestions?

Answer 1

Say you have A2:A20 list of names and B2:B20 list of grades(numbers) the following array formula will rank the way you want
=IF((SUM(IF( $B$2:$B$20=B2,1))) =1,(SUM(IF( $B$2:$B$20>=B2,1,0))),( SUM(IF($B$2:$B$20 >=B2,1)))-(( SUM(IF( $B$2:$B$20=B2,1))) -1)*0.5)
put the formula in B2, hit F2, then Ctrl+Shift+Enter to activate it as array formula, copy it down the column to rank all the numbers
so what you get is if 2 persons of the same grade, the rank suppose for them say 5 & 6 will become 5.5 for each person, if 3 persons of same grade take up 5 & 6 & 7 then each of them will get 6 as the rank.
Hope this helps.

Answer 2

Look this video will help you

https://www.youtube.com/watch?v=QOAxLbTLdh8&feature=youtu.be