Deduct Highests Scores By Condition

[antonio]

Hello,

Would appreciate your help...I'm setting up a template for Atlantic golf scoring. I have everything working but stuck at the level of deducting points.

Points will be deducted from total highest scores based on the below table condition. What is the formula that I can use under column "X" of the attached file & also perform a conditional formatting of values?

This is the "Atlantic Scoring" method of calculation.

How points are deducted is that you take your gross score & deduct your highest score per hole following the table.

Column Z is indicating amount of holes that required to be deducted.

Example:
For player 1

Gross score: 81

Highest scores per hole
Hole 16 =7
Hole 14 =6 *calculated at .5

.5 calculation for hold 14 = (6/2)= 3

7+3=10

81-10
Atlantic score = 71

What is the best method for me to do this?

Deduction Table by highest scores:
0 - 72 = 0
73 - 75 = 0.5
76 - 80 = 1
81 - 85 = 1.5
86 - 90 = 2
91 - 95 = 2.5
96 - 100 = 3
101 - 104 = 3.5
105 - 109 = 4
110 - 114 = 4.5
115 - 119 = 5
120 - 124 = 5.5
125 - 129 = 6
130 - 134 = 6.5
135 - 139 = 7
140 - 144 = 7.5
145 - 149 = 8
150 - 154 = 8.5
155 - 159 = 9