Spreadsheet: 674455d1587851865-calculate-average-from-lowest-8-values-from-a-maximum-range-of-20-events-copy-of-example1676_vb
Sheet: STATS

 |  | CALCULATED HANDICAPS |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | DIFFERENTIALS (CALCULATED VIA OTHER MEANS) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | NUMBERS OF EVENTS PARTICIPATED IN (FOR HANDICAP PURPOSES, MAX EVENTS TO REFERENCE IS '8' EVENTS |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ACTUAL NUMBER OF EVENTS PARTICPATED IN
EVENT
HDCP_ROUNDS |  | H01 | H02 | H03 | H04 | H05 | H06 | H07 | H08 | H09 | H10 | H11 | H12 | [NAME] | H14 | H15 | H16 | H17 | H18 | H19 | H20 | H21 | H22 | H23 | H24 | H25 | H26 | H27 |  | FW8 | FW7 | FW6 | FW5 | FW4 | FW3 | FW2 | FW1 | D01 | D02 | D03 | D04 | D05 | D06 | D07 | D08 | D09 | D10 | D11 | D12 | D13 | D14 | D15 | D16 | D17 | D18 | [NAME] | D20 | D21 | D22 | D23 | [NAME] | D25 | D26 | D27 |  | PC8 | PC7 | PC6 | PC5 | PC4 | PC3 | PC2 | PC1 | CC01 | CC02 | [NAME] | CC04 | CC05 | CC06 | CC07 | CC08 | CC09 | CC10 | CC11 | CC12 | CC13 | CC14 | CC15 | CC16 | CC17 | CC18 | CC19 | CC20 | CC21 | CC22 | CC23 | CC24 | CC25 | CC26 | CC27 |  | ROUNDS
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 | 5.8 | 6.1 | 6.5 | 4.8 | 8.5 | 6.5 | 5.8 |  |  |  |  |  |  |  |  |  | 1
[NAME] (Avg Low 8 of last Max 20) |  |  |  |  |  |  |  |  |  | 11 | 9.8 |  | 8.9 | 7.5 |  | 7 | 7 | 6.9 | 6.9 | 6.8 | 6.7 | 6.2 | 5.9 | 6.3 | 5.8 | 6.4 | 6.1 | 6.1
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ^^ starts to break when there are now more than 20 rounds and we start dropping off older games
DATA EXPLAINED
 | [NAME]: These values (GOLF DIFFERENTIALS) could be brought forward from previous year, manual data entries
 | AF3-AY3: These values (GOLF DIFFERENTIALS) that are calculated based on other data not included in this example.  They are hard-coded in this instance.  Values can be + or - values including ZERO and they MAY BE BLANK if a player missed an event.
 |  | THE MOST RECENT DIFFERENTIALS are to the right (Eg. AN3 is more recent than AM3 and AM3 is more recent than AL3)
 | MY PROBLEM LIES IN THE CALCULATIONS IN FIELDS C3-V3.  They work correctly C3-J3, but the wrong value appears in K3-V3 (See SCENARIO BELOW)
 |  | Rather than using the lowest 8 of the last 20 rounds, its doing an average of the last 8 rounds
 | BA3-BH3: These are helper fields that count the number of GOLF DIFFERENTIALS (running total) that exist in columns [NAME] so that we always know how many scores a player has tallied
 | BI3-BH3: As described above, these are helper fields counting GOLF DIFFERENTIALS, but there is a RULE in place that limits the maximum value of 8.
 | CD3: Total number of GOLF DIFFERENTIALS (running total) that does NOT take into account the RULE described above.
 | A3 this is merely a copy of CD3 with RULE applied that limits total GOLF DIFFERENTIALS to a maximum value of 20
SCENARIO | In COLUMNS [NAME], I'm calculating the current Player Handicap based on their recent play which is represented in COLUMNS [NAME]
 | Basic concept is that I need to take the 'most recent' LOWEST 8 SCORES from [NAME] from the 'most recent' 20 games.
 | If I don't have 8 LOWEST SCORES, I take the average of what scores are available UP TO A MAXIMUM of 8 scores
 | If I don't have 20 most recent Games, I take the 8 lowest scores from the most recent games that are available up to 20 games.  Games can be listed BLANK because a player missed an event and therefore a MISSED event does NOT count as part of the range of 20 games
 | I am calculating a GOLF HANDICAP which is an AVERAGE of the (LOWEST "A" VALUES) from the (MOST RECENT "B" RANGE) of Differentials
 |  | The "A" VALUE Differential must not be blank and must not be greater than 8
 |  | The "B" Value must not be blank and must be GREATER THAN 2 and LESS THAN OR EQUAL TO 20
MANUAL |  | EVENT >> |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | # SCORES USED |  |  | MANUAL HANDICAPS |  |  |  |  | TRANSLATION OF CALCULATION
EXAMPLES |  |  |  | 3.5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1 SCORE in 1 EVENT lowest 1 used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2 SCORES in 2 EVENTS lowest 2 from MOST RECENT 2 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 3 SCORES in 3 EVENTS lowest 3 from MOST RECENT 3 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 4 SCORES in 4 EVENTS lowest 4 from MOST RECENT 4 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 5 SCORES in 5 EVENTS lowest 5 from MOST RECENT 5 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 6 SCORES in 6 EVENTS lowest 6 from MOST RECENT 6 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 7 SCORES in 7 EVENTS lowest 7 from MOST RECENT 7 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 8 SCORES in 8 EVENTS lowest 8 from MOST RECENT 8 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 9 SCORES in 9 EVENTS lowest 8 from MOST RECENT 9 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 10 SCORES in 10 EVENTS lowest 8 from MOST RECENT 10 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 10 SCORES in 11 EVENTS lowest 8 from MOST RECENT 11 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 11 SCORES in 12 EVENTS lowest 8 from MOST RECENT 12 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 12 SCORES in 13 EVENTS lowest 8 from MOST RECENT 13 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 12 SCORES in 14 EVENTS lowest 8 from MOST RECENT 14 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 13 SCORES in 15 EVENTS lowest 8 from MOST RECENT 15 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 14 SCORES in 16 EVENTS lowest 8 from MOST RECENT 16 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 15 SCORES in 17 EVENTS lowest 8 from MOST RECENT 17 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 16 SCORES in 18 EVENTS lowest 8 from MOST RECENT 18 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 17 SCORES in 19 EVENTS lowest 8 from MOST RECENT 19 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 18 SCORES in 20 EVENTS lowest 8 from MOST RECENT 20 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 | 5.8 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 19 SCORES in 21 EVENTS lowest 8 from MOST RECENT 20 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 | 5.8 | 6.1 |  |  |  |  |  |  |  |  |  |  |  |  |  | 20 SCORES in 22 EVENTS lowest 8 from MOST RECENT 20 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 | 5.8 | 6.1 | 6.5 |  |  |  |  |  |  |  |  |  |  |  |  | 21 SCORES in 23 EVENTS lowest 8 from MOST RECENT 20 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 | 5.8 | 6.1 | 6.5 | 4.8 |  |  |  |  |  |  |  |  |  |  |  | 22 SCORES in 24 EVENTS lowest 8 from MOST RECENT 20 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 | 5.8 | 6.1 | 6.5 | 4.8 | 8.5 |  |  |  |  |  |  |  |  |  |  | 23 SCORES in 25 EVENTS lowest 8 from MOST RECENT 20 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 | 5.8 | 6.1 | 6.5 | 4.8 | 8.5 | 6.5 |  |  |  |  |  |  |  |  |  | 24 SCORES in 26 EVENTS lowest 8 from MOST RECENT 20 EVENTS used for HANDICAP calculation
 |  |  |  | 3.5 | 12.6 | 3.5 | 20.7 | 6.2 | 17.1 | 26.1 | 15.3 | 8.9 | 11.4 |  | 9.4 | 4.7 |  | 8.6 | 11 | 10 | 10.2 | 9.8 | 8.7 | 5.8 | 6.1 | 6.5 | 4.8 | 8.5 | 6.5 | 5.8 |  |  |  |  |  |  |  |  | 25 SCORES in 27 EVENTS lowest 8 from MOST RECENT 20 EVENTS used for HANDICAP calculation
 |  | KEY >> |  |  | Game not yet played
 |  |  |  |  | Differential used in the handicap calculation (amongst 8 lowest scores in the maximum range of 20 scores)
 |  |  |  |  | Differential NOT used in the handicap calculation (Not amongst LOWEST 8 in range)
 |  |  |  |  | Score is more than 20 games in past and no longer used for calculation
 |  |  |  |  | Maximum range of 20 events played (Eg. 20 Scores received with no cells blank)
 |  |  |  |  | Maximum number of scores to be used for calculation.  LOWEST (# of scores) from the applicable range
 |  |  |  |  | Player missed this event, no score posted (needs to be SHIPPED/IGNORED in calculations)