Golf Formula Help

[LOU]

I am trying to write this in excel can someone help me with this. I have
tried the if and but no success.

The criteria are as follows.

Handicaps. 3 scores are used with the oldest score dropping
out when the new round is played. 90% of a gross handicap between 0 -
10 for the net; 85% of 11 - 20; 80% of 21 - 30 and lastly 75% of 31 -
40.

 

[DOR]

You need to describe your requirements a little better - are you trying
to calculate the total net score for the last three rounds, with the
net score for each round being calculated as the Gross score for that
round minus Adjusted Handicap, where Adjusted Handicap is 85% of actual
handicap for handicaps 0-10, 80% of actual for 11-20 and 75% of actual
for handicaps greater than 20?

Or are you trying to calculate new handicaps based on the last three
rounds, and, if so, could you explain your rules for calculating new
handicaps?

DOR

 

[Biff]

Hi!

It's not clear what you want to do.

The first question I have is: what if there aren't 3 scores?

Suppose there are 3 scores: 75, 77, 70.

The player has a 2 hdcp.

Then what?

How about some examples.

Biff

 

[DOR]

This may work for you, if you are not interested in rounding each
handicap after the percentage is calculated:

Put your gross score in A2 and downwards - start with three scores in
A2, A3, A4. Put the full handicap that applied for each score beside
it in column B (I am assuming that a given player can have a different
handicap for each score as happens in the normal course of events).

Enter this formula in C4, as an array formula, that is, confirm it with
Shift-Control-Enter rather than just Enter:

=SUM(A2:A4)-SUM(B2:B4*(IF(B2:B4<=10,0.9,IF(B2:B4<=20,0.85,IF(B2:B4<=30,0.8,0.75)))))

Drag this formula down as far as you have scores. That will give you a
running total for the last three scores.

If you want to round the handicaps before subtracting from the gross
score (as is normally done with adjusted handicaps), then use column C
to show the net score for each round, by putting this formula in C2 and
dragging down:

=A2-ROUND(IF(B2<=10,0.9,IF(B2<=20,0.85,IF(B2<=30,0.8,0.75)))*B2,0)

Now create a running total starting in D4, using the formula:

=SUM(C2:C4), and copy down.

If you want to be fancy and and not use a running total or helper
columns, then let us know. This is the simple solution. I hope my
interpretation of the problem is correct.

Note that you could also store the break points and percentages as
variables, which would be preferable if you ever want to change them,
but note that if you set them up as simple variables and ever change
them, all the past history is subject to change also, unless you take
steps to freeze it in some way.

DOR