Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding (Multiple) Highest Values in Column
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 |
#2
|
|||
|
|||
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? In article , Shay Hurley wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to lookup Multiple Column Text and then Count Result | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
counting Multiple answers in 1 cell + column | Excel Discussion (Misc queries) | |||
counting Multiple answers in 1 cell + column | Excel Discussion (Misc queries) | |||
How sum values in column B using values in column A as the conditi | Excel Worksheet Functions |