Deduct Highests Scores By Condition

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

How come for player 1 & 3 you are taking only 2 cells for deduction, and for 2 & 4, 4 cells?

Points are to be deducted depending on the gross score of players.

Depending on the what the player obtained as highest score.

Example on Player 4
He needs to be deducted = 3.5 from highest score

Holes:7 score » 9, 9 score » 8, 11score » 8 & 16 score » 8(Hole 16 is to be divided by 2 = 8/2=4)
Total deduction: 29

Net score: 101-29= 72

G'day,

After scratching my head for a while I realised what your trying to explain. Then scratched my head some more to make it work automatically and now I have a large gaping hole in my head. Hahahaha.

Then this workbook popped out.

Edit: Did not allow player to be under Par. Please replace the formula in cell X10 with this one.

Code:

---

=IF(Z10=0,W10,W10-IF(Z10=0.5,OFFSET('Scoring Database'!A3,0,VLOOKUP(Z10,Half,2,FALSE))/2,SUM('Scoring Database'!A3:OFFSET('Scoring Database'!A3,0,AB10-1))+IF(ISNA(VLOOKUP(Z10,Deduction,1,FALSE)),0,OFFSET('Scoring Database'!A3,0,VLOOKUP(Z10,Half,2,FALSE)-1)/2)))

---