Ranking in Excel

Once your spread sheet is made consider the highest amount you may accumulate and set for how many zero's for placeholders. Click on the column top and then click on Z-A. This should rank them by standings.

You have to make sure they have the same amount of digits and that zeros are not eliminated. Otherwise you will get such as follows.

mike 600

chaz 500

sue 4000

To do this click on the top of the column and then click on "format" "cells".

Then click on the "text" and hit "okay"

Once all your data is entered and you hit the column descending it will rearrange to your liking. Hitting column ascending will reverse it.

ah, I know how to format cells and align columns. What I need is more complicated and will probably need several functions together. Below the list of names and their values, I need to see:

<in cell A10>3rd place: <in cell B10> Mike <in cell C10> 1000

<in cell B10>2nd place .......... etc

Whenever I change the numbers, the contents of cells B10 and C10 must automatically update. If John's sales figure becomes 2000 in the cells above row 10, the result shown in B10 changes to John and C10 now shows 2000.

To make it more complicated, if John's figure becomes 1000 then B10 shows Mike, B11 shows John, C10 and C11 both show 1000 because they are joint winners.

I also want it to show second and third place too. I think I need the LARGE and RANK functions and maybe others.

it seems a trace function can do the trick.

All sorted now. You need the RANK function to give each person their ranking. Then you need the VLOOKUP function to find the ranking of 1 and print the name next to it. Same for 2, 3 and as far as you wish to go.

Finally you need the COUNTIF function to allow for people who have the same score. This is because if there are, for example, two people equally in second place, Excel ranks them as 1 2 2 4 5 6... etc. There is no ranking of 3 and the VLOOKUP falls over at that point. COUNTIF allows for that, but giving 1 2 3 4 5... isn't perfect if the ranking 2 and 3 are in fact the same amounts.

Getting there, slowly...