Trying to improving the functionality of a workbook

**Marvo** · 03-24-2024, 02:37 PM

I've copied out the rules in regards handicap changes after each golf event. I have applied these to my workbook in a block of cells BG1:BO68 on the Master sheet. It does work but as I'm infamous on here for being messy and taking 12 complicated slow steps to achieve something that could be done in 2 or 3 simple ones, I wondered whether anybody would like to take a look at it to see if it could be improved?

I know its a bit of a chore but I also know some of you like a challenge.

Guidelines.

1) Players are divided into 4 Categories by handicaps where any adjustments are made up and down. (Column BO)
Cat 1. Handicap 0-9. Multiples of 0.1
Cat 2. Handicap 10-19. Multiples of 0.2
Cat 3. Handicap 20-29. Multiples of 0.3
Cat 4. Handicap 30 and over. Multiples of 0.4

Handicap cuts:
Playing to your handicap should produce a score of 36 pts. Therefore, anybody scoring more than 36pts will receive a cut dependent on
1) Category of golfer
2) how many points they score above 36.

Examples:
A player with a handicap of between 10 and 19 scoring 38 pts would result in a cut of 0.4. Thatï¿½s 2 x 0.2.
A player with a handicap of over 30 with the same score would result in a cut of 0.8. Thatï¿½s 2 x 0.4.

Handicap cuts for prize winners are.
1st = a cut of 15%. 2nd = a cut of 10%. 3rd = cut of 5%. (Column BJ)

Other than the Three winners or players scoring above 36, players who score within 7 pts of the winning score will receive no handicap adjustment.

Players more than 7pts behind the winner will receive an increase in their handicap. The amount will be dependent on
a) Their category.
b) Balancing the number of cuts with increases.

Simplest example:
28 players.
1st 36pts. 20 handicapper (cut 3 shots)
2nd 35pts. 20 handicapper (cut 2 shots)
3rd 34pts. 20 handicapper (cut 1 shot)
4th 33pts (no change)
5th 32pts (no change)
6th 31pts (no change)
7th 30pts (no change)

The remaining 21 players would receive a handicap increase = in total the 6 shots cut, keeping the society in balance.
If all received the same this would entail a 0.3 increase for all, however category of golfer is taken into the equation so a 15 handicapper may get an increase of 0.2, a 35 handicapper 0.4.

Many thanks in advance.

**Marvo** · 03-25-2024, 12:46 PM

I've been attempting to do this myself with some success but also some abject failure. However if I could get some assistance in regard the following formula which increases a players handicap.

=MIN(1,ROUND(IF(A18="✖",0,IF(AQ18<$BI$3,BN18+$BK$2,0)),1))

This is in Cell BL4 of the attached workbook, worksheet Master

AQ is the players score and BI3 is the winning score minus 6. Finishing positions are in Column BB.

So if you finish with a score 6 or more less than the winner you get a percentage handicap increase. However I need to add to the formula to state:

1) Unless you finish second or third, so win prize money.
2) Unless you finish with more than 35 points.

The highest placed person to get an handicap increase would be someone more than 6 pts behind the winner, who has not finished in the first three places and has not scored more than 35 points.

Thank you in anticipation.