Good morning all,
I'm fairly new to Excel, so please excuse if this question cannot be completed. I have a worksheet for results (golf society points leaderboard), where column A is a generic list of current members, this is then followed by 8 columns of results, which lead to a total points column (column K), number of rounds played (column L), total points of best 6 results (column M), average points per event (column N), number of victories (column O). I am trying to auto update a leaderboard with the names of members, from column A, dependant on the largest value from column M, if two or more members have the same value here, I would like the compare to then take place on column N and finally column O. At the minute, I have been able to get column M compared to index the members but when values are equal it populates the same member numerous times in the leaderboard.
Hopefully I have explained that well enough. I have uploaded the spreadsheet to help try and understand my query. Again if this cannot be done then apologies for posting.
I'm fairly new to Excel, so please excuse if this question cannot be completed. I have a worksheet for results (golf society points leaderboard), where column A is a generic list of current members, this is then followed by 8 columns of results, which lead to a total points column (column K), number of rounds played (column L), total points of best 6 results (column M), average points per event (column N), number of victories (column O). I am trying to auto update a leaderboard with the names of members, from column A, dependant on the largest value from column M, if two or more members have the same value here, I would like the compare to then take place on column N and finally column O. At the minute, I have been able to get column M compared to index the members but when values are equal it populates the same member numerous times in the leaderboard.
Hopefully I have explained that well enough. I have uploaded the spreadsheet to help try and understand my query. Again if this cannot be done then apologies for posting.
20210401-Monthly Medal Results_TEST.xls | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | MONTHLY MEDAL POINTS | |||||||||||||||||||||
2 | Apr-21 | May-21 | Jul-21 | Aug-21 | Sep-21 | Oct-21 | Nov-21 | Jan-22 | Feb-22 | POINTS TOTAL | NUMBER OF EVENTS PLAYED | BEST 6 RESULTS | AVERAGE POINTS PER EVENT (Tied Result 1) | NUMBER OF VICTORIES (Tied Result 2) | ||||||||
3 | ||||||||||||||||||||||
4 | NAME | OVERALL STANDINGS TOUR CHAMPIONSHIP | ||||||||||||||||||||
5 | Member 1 | 0 | 9 | 0 | 7 | 0 | 0 | 0 | 0 | 0 | 16 | 2 | 16 | 8.000 | 0 | |||||||
6 | Member 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | POSITION | NAME | ||||||
7 | Member 3 | 0 | 0 | 0 | 6 | 0 | 0 | 0 | 0 | 0 | 6 | 1 | 6 | 6.000 | 0 | 1 | Member 1 | |||||
8 | Member 4 | 0 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 1 | 10 | 10.000 | 1 | 2 | Member 10 | |||||
9 | Member 5 | 0 | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | 1 | 7 | 7.000 | 0 | 3 | Member 4 | |||||
10 | Member 6 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | 1 | 8 | 8.000 | 0 | 4 | Member 4 | Should be Member 17 | ||||
11 | Member 7 | 0 | 5 | 0 | 5 | 0 | 0 | 0 | 0 | 0 | 10 | 2 | 10 | 5.000 | 0 | 5 | Member 4 | Should be Member 7 | ||||
12 | Member 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
13 | Member 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
14 | Member 10 | 0 | 6 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | 14 | 2 | 14 | 7.000 | 0 | |||||||
15 | Member 11 | 0 | 3 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 7 | 2 | 7 | 3.500 | 0 | |||||||
16 | Member 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
17 | Member 13 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 1 | 4 | 4.000 | 0 | |||||||
18 | Member 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
19 | Member 15 | 0 | 0 | 0 | 9 | 0 | 0 | 0 | 0 | 0 | 9 | 1 | 9 | 9.000 | 0 | |||||||
20 | Member 16 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
21 | Member 17 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | 0 | 0 | 10 | 1 | 10 | 10.000 | 1 | |||||||
22 | Member 18 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 3 | 1 | 3 | 3.000 | 0 | |||||||
23 | Member 19 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
24 | Member 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
OVERALL STANDINGS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K5:K24 | K5 | =SUM(B5:J5) |
L5:L24 | L5 | =COUNTIF(B5:J5,"<>0") |
M5:M24 | M5 | =SUM(LARGE(B5:J5,{1,2,3,4,5,6})) |
N5:N24 | N5 | =IF(L5<>0,(SUM(K5/L5)),"") |
O5:O24 | O5 | =COUNTIF(B5:J5,"10")+COUNTIF(B5:J5,"20") |
S7 | S7 | =INDEX(A5:A34,MATCH(LARGE(M5:M34,1),M5:M34,0)) |
S8 | S8 | =INDEX(A5:A34,MATCH(LARGE(M5:M34,2),M5:M34,0)) |
S9 | S9 | =INDEX(A5:A34,MATCH(LARGE(M5:M34,3),M5:M34,0)) |
S10 | S10 | =INDEX(A5:A34,MATCH(LARGE(M5:M34,4),M5:M34,0)) |
S11 | S11 | =INDEX(A5:A34,MATCH(LARGE(M5:M34,5),M5:M34,0)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |