Finding (Multiple) Highest Values in Column

[Shay Hurley]

Hi,

I have a golf spreadsheet that calculates stableford points based on
someones score on a hole, the index of that hole and the players
handicap. It works fine, calculating up to 50 players points totals. Now
I would like to calculate the best points total for the first 9 holes.

My spreadsheet is as follows:

ColA ColB ColC ColD ... ColI
H1Score H2pts H2Score H2pts ... Pts9total
..
..
..

Of course using the Max function for ColI would give me the highest
score in that column but what if there are 2 people with the same points?

So now to my question, how can I determine the highest points total in
ColI and if there is more than one row with the same (highest) points?
Also I would need to find out the rows themselves so I can do another
calculation.

Thanks in advance,
Shay

 

[Domenic]

Assumptions:

A1:K50 contains your data

First row contains your headers/labels

Column A contains the golfer's name

Columns B through J contains the score for each hole

Column K contains the total

Formulas:

L2, copied down:

=RANK(K2,$K$2:$K$50)+COUNTIF($K$2:K2,K2)-1

M1: enter 1, indicating you want the top golfer and score

N1:

=MAX(IF(K2:K50=INDEX(K2:K50,MATCH(M1,L2:L50,0)),L2:L50))-M1

....confirmed with CONTROL+SHIFT+ENTER

O2, copied down:

=IF(ROWS(O$2:O2)<=$M$1+$N$1,MATCH(ROWS(O$2:O2),$L$2:$L$50,0),"")

P2, copied across and down:

=IF(N($O2),INDEX(A$2:A$50,$O2),"")

Note that if, for example, you want a Top 5 list, change the 1 in M1 to
5.

Also I would need to find out the rows themselves so I can do another
calculation.

Can you elaborate?