Picking top 5 scores from a range

Question

I run a small golf eclectic with excel. One of the things we have is a points system. I would like to get the 5 highest points scored over the season and have them ranked from 1 (being the highest points scored) to 5.

My knowledge of excel "sums" goes only a wee bit further than add and subtract.

Thanks!

Solution 1

If you don't want to change the order that they are presently in you can use the LARGE function. It returns the kth largest value.

Below is a great formula, if you drag it down it will automatically get the second, third and nth largest value from a table of data (in this example the data is between A1 to A10).

=LARGE(A1:A10,ROW(A1)-ROW($A$1)+1)

You can then match the values with names or corresponding data from the tables using the MATCH and INDEX functions. The example below would fetch the name for each value from the second column.

=INDEX($A$1:$B$10,MATCH(cell reference with score or value,$A$1:$B$10,2))

Play around with these formulas, they are very convenient for data m

Solution 2

If you have a column containing the scores, you could add a filter (Data->Filter I think) and sort descending.

Though, if you just have rows that are something like [Date][Person][Score] you'll need to go to another sheet and SUM the scores for each person then sort that... Unfortunately my Excel skills aren't up to par to pull a score for each person like that.

Solution 3

Given a list of numbers in A1 to A10, you can work out their 'Rank' relative to each other by using 'RANK'.

e.g.

RANK(A1,A1:A6,0)

RANK(cell, list of cells to check against, order)

For order, 0 = descending.

From there you can work out which one is first pragmatically.