Calculate and revise Handicaps every week of a 30 week season. Multiple criteria, can't figure out formula to make it work.

[golden21060]

Objective:
Calculate and revise Handicaps every week of a 30 week season based on the scores posted from of the previous weeks. The 30 week season starts 4/7/16 and ends 10/27/16.

Key Data Captured:
1. Each row has individual member’s information.
2. Each Column has specific information on team members as follows:
Cell(s) Column Location
· # of Rounds Played “B”
· Initial Handicap “M”
· Weekly Scores “T” Thru “AW” (30 weeks)
· Revised Handicaps “AY” thru “CB” (30 weeks) established by this formula.



SCORING RULES:

1. For your first 3 rounds, we will use the handicap in column “M”.
2. Your handicap for your 4^th & 5^th rounds will be the average of your first 3 scores, -72.
3. Your handicap for all rounds after your 5th will be the average of your last 5 scores, -72.

Solution:
· To determine the handicap for this week, look at the # of rounds played (Col “B”), and the scores from previous weeks.
· If less than 4 rounds played, use rule #1.
· If 4 or 5 rounds played, use rule #2.
· If more than 5 rounds played, use rule #3

Challenge:

The conditions can occur during any week of the season. E.g., the first 3 rounds might not occur until week 5 if the player only plays week 1, 3, & 5, and skips weeks 2 & 4. Likewise, rounds 4 & 5, or 6+ could occur anytime in the season. So, the formula must also test for zeros in the score columns.
1. If Condition 1 is met (rounds 4>), use handicap in Col “M”.
2. If Condition 2 is met (rounds = 4 or 5), Use the average score of the 1^st 3 scores -72. Begin by looking in W1 and forward and use 1^st 3 scores that are not zero.
3. If Condition 3 is met (>5 rounds), use the average of the last 5 rounds, that are not zero, -72. Starting in the current week and looking back through the last 5 weeks that are not zero.


See examples below.

E.G. Use the scores in Red text for the 6 conditions shown below.

Rule	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	# Rounds	Average		Week 8 H'cap
1	85	0	0	0	83	0	0	2	84		Use Initial HC
1	85	0	89	0	83	0	0	3	86		Use Initial HC
2	0	92	95	0	91	95	0	4	93	-72	21
2	82	81	85	83	0	0	85	5	83	-72	11
3	82	83	82	0	89	85	83	6	84	-72	12
3	90	89	92	93	88	87	85	7	89	-72	17

<tbody>
</tbody>

Note: When using an average, round up (E.G an average of 85.6 = 86. And an average of 93.25 = 93.

 

[candybg]

Is this a homework assignment?

 

[golden21060]

No, it isn't a homework assignment. It's a request from a friend for a golf league and he put together the notes. I've struggled with a starting point for a formula with all the criteria. Is it one big if statement?