Bernie,

I can't thank youo enough. You've taken the time to explain, in great
detail, all the areas where I had problems.

You've persisted with me, when I have perhaps asked stupid, pointless
questions.

Many thanks for all your help

Duncs

On 3 Jul, 23:01, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

You're right - I forgot to add 1, as in RawScore - CoursePar + 1

For the maximum score on the table, 130 for a par of 72, that should be

130 - 72 +1, or 59

So,

HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2

should be

HAdj = ((Application.Min(RawScore - CoursePar + 1, 58 + 1)) Mod 5) - 2
Or
HAdj = ((Application.Min(RawScore - CoursePar + 1, 59)) Mod 5) - 2

So it should be 88 (not 87). As to the 88 versus 94 - the part that I
original missed was

"And finally, the maximum a golfer can deduct under the Callaway System is
50 strokes."

so 144 - 50 is 94, and is the correct return in this case.

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

ups.com...
Bernie,

I'm really getting lost with this now!

Can I take it from the top, so that I'm clear myself in what I'm
saying?

Column B has the following values in it: 5, 4, 4, 5, 3, 5, 4, 3, 4,
4, 3, 4, 5, 4, 3, 4, 5, 3

Column C has the following values in it: 10, 8, 8, 10, 6, 10, 8, 6, 8,
8, 6, 8, 10, 8, 6, 8, 10, 6

As you will see, the scores for each hole are double the hole pars.

So, using your code we have:

Callaway = RawScore
Callaway & RawScore = 144

'Calculate Adjustments
If RawScore CoursePar Then

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5)
* 0.5 + 0.5
NumAdj = 6

'Subtract the highest scores from the first 16 holes only
For i = 1 To Int(NumAdj)
Callaway = Callaway -
Application.WorksheetFunction.Large(UsedScores, i)
Next i

Out of here, Callaway = 88

'Possibly, use half the holes score (rounded up to a whole
number)
If NumAdj < Int(NumAdj) Then
Callaway = Callaway -
Application.RoundUp(Application.WorksheetFunction. Large(UsedScores,
NumAdj + 0.5) / 2, 0)
End If
End If

'Calculate Final Handicap Adjustment
HAdj = 0
If RawScore CoursePar + 3 Then
HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2

Within here, HAdj = (Min(144 - 72 + 1, 58) Mod 5) - 2
HAdj = (Min(73, 58) Mod 5) - 2
HAdj = (58 Mod 5) - 2
HAdj = 3 - 2
HAdj = 1
End If

If RawScore CoursePar And RawScore <= CoursePar + 3 Then
If (144 72 = TRUE) AND (144 <= 75 = FALSE)
HAdj = RawScore - CoursePar - 3
End If

'Output final Callaway score
Callaway = Application.Max(RawScore - 50, Callaway - HAdj)
Callaway = Application.Max(144 - 50, 88 - 1)
Callaway = Application.Max(94, 87)
Callaway = 94

However, as I said previously, I think the HAdj value should be 2, and
that the function should return the value of 88, or am I missing
something?

Help!

Duncs

On 3 Jul, 17:31, "Bernie Deitrick" <deitbe @ consumer dot org wrote:



Duncs,

However, I believe this to be incorrect.
I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

The 2 is the case if you set the maximum stroke level as Par + 58 - since
the raw score is higher,
then the 2 is returned (in the adjusted code)

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

No. The maximum reduction is 50, based on the rules of the Callaway
system, so a raw score of 144
can only be reduced to 94.

Am I making sense?

As much as I am ;-)

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

roups.com...

Bernie,

I agree, your way looks far nicer and more compact.

However, it still doesn't provide an answer to the other issues I
raised in my reply.

Can you advise? Many thanks for your help on this...bet you wish you
hadn't answered my original post?

Rgds

Duncs

On 3 Jul, 16:08, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

'Calc the number of highest holes that need to be subtracted
NumAdj = Application.Min(Int((RawScore - CoursePar + 1) / 5) * 0.5
+ 0.5, 6)

Now, NumAdj = 6

Better would be:

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5) * 0.5
+ 0.5

and then

'Calculate Final Handicap Adjustment
HAdj = 0
If RawScore CoursePar + 3 Then
HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2
End If
If RawScore CoursePar And RawScore <= CoursePar + 3 Then
HAdj = RawScore - CoursePar - 3
End If

HTH,
Bernie
MS Excel MVP

So, the code should now subtract the 6 highest scores, in this case
these are 10 x 4 & 8 x 2, which equals 56.

So, the Callaway variable now holds the value 144 - 56 = 88

There is no need to use half the holes score, so it works out the
Handicap Adjustment as 1.

Finally, the function result is calculated as the Max between:

RawScore - 50 = 94 & Callaway - HAdj = 87

So, the function returns the value 94.

However, I believe this to be incorrect.

I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

Am I making sense?

Rgds

Duncs

On 3 Jul, 12:58, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

My version doesn't use those values, and I'm not sure how you think
you are using those
values,
either.

To check which scores are used, change the function code to include
this:

'Store the used scores from the first 16 holes for later use
'in adjusting the returned score
For Hole = 1 To HoleScores.Cells.Count
If Hole <= HoleScores.Cells.Count - 2 Then
UsedScores(Hole) = Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
End If
RawScore = RawScore + Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
MsgBox "On hole #" & Hole & ", the score used was " & _
Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

roups.com...

Bernie,

I'm not following this!

I've made the amendment to the code as you mentioned, and I now
have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the
same,
and the hole scores are double the par's.

There is still the concern of the code in the Callaway
function...I
find that the scores passed through are 1 less than the scores on
the
spreadsheet. So, it is actually using the values, in HoleScores,
of
9, 7, 7, 9, 5, 9, 7, 5, 7, 7, 5, 7, 9, 7, 5, 7, 9, 5. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding
of
something simple!

Rgds

Duncs

On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Duncs,

I assumed that the pattern of holes to subtract continued, so for
a raw
score of 144, then the 7 1/2 worst holes could be subtracted. If
we limit
the adjustment to 6 holes max, then the adjusted score would be
87.

But I did miss the maximum adjustment of 50 strokes - the last
line should
be

'Output final Callaway score
Callaway = Application.Max(RawScore - 50, Callaway - HAdj)

Let me know if the limit is really 6 holes or not... it is easy
to adjust
the code to account for it.

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

groups.com...

Bernie,

I have another problem with the spreadsheet you sent, that I
hope you
can help with.

As mentioned in a previous post, I have the following Par's
entered in
Column B: 5, 4, 4, 5, 3, 5, 4, 3, 4, 4, 3, 4, 5, 4, 3, 4, 5, 3

I now have the following scores in Column C: 10, 8, 8, 10, 6,
10, 8,
6, 8, 8, 6, 8, 10, 8, 6, 8, 10, 6

This give a raw score of 144, and a Callaway Score of 75.
However, I
think this score should be 86.

If I then trace through the code for the Callaway function, I
find
that the scores passed through are 1 less than the scores on
the
spreadsheet. So, it is actually using the values, in
HoleScores, of
9, 7, 7, 9, 5, 9, 7, 5, 7, 7, 5, 7, 9, 7, 5, 7, 9, 5.

Can you advise?

Rgds

Duncs

On Jun 16, 5:17 pm, "Duncs" wrote:
Bernie,

Thanks for getting back to me. What was wrong / confusing me
was the
calculation for the raw score. Now that this is fixed, it
works

...

read more »- Hide quoted text -

- Show quoted text -