Excel 97 - golf average

[Frith]

Someone asked me to calculate a golf average, specifically, the average of the best 10 scores in the last 20. A function would probably be the best solution (GOLFAVG (A1:A20)) with a range where the scores would be found. If less than 10 scores, the average of the 10 scores would be used. If more than 10 but less than 20, then the average of the 10 best scores.
I've tried writing a function and tried writing a macro with no success in either method. Any help would be appreciated. Doug Frith
Accord Communications

 

[paulovey]

Hi,

This will require some user intervention but it gets the solution.

Just get the user to sort in ascending order of golf scores.

Then have a macro that looks at a set range of cells, being the 10 best scores, for example b1:b10.

When the scores in the column B are sorted on value the best ten will appear at the top of the list and, already have a macro looking at this range to give the avg.

Paul

 

[DarkSun]

Here is the function. A bit messy but it should do the trick.

Public Function GOLFAVG(MyRange As Range)
Dim Counter As Integer
Dim MySum As Integer
Dim AryScores() As Integer

MySum = 0
Counter = 1
For Each c In MyRange
ReDim Preserve AryScores(Counter)
AryScores(Counter) = c.Value
Counter = Counter + 1
Next
Counter = Counter - 1
For i = 1 To UBound(AryScores()) - 1
For x = 1 To UBound(AryScores()) - 1
If AryScores(x) > AryScores(x + 1) Then
Temp = AryScores(x + 1)
AryScores(x + 1) = AryScores(x)
AryScores(x) = Temp
End If
Next x
Next i

If Counter > 10 Then Counter = 10

For i = 1 To Counter
MySum = MySum + AryScores(i)
Next i

GOLFAVG = MySum / Counter
End Function

Excel syntax. =GOLFAVG(Range)

Example: =GOLFAVG(A1:A20)

This also works if the range is less than 10 scores, it just takes the average of the available scores.

 

[JVFriederick]

Since variety is the spice of life :

A B C
SCORE # RANK
68 1 59
72 2 66
88 3 68
85 4 68
59 5 72
76 6 76
87 7 76
90 8 85
66 9 87
88 10 87
76
68
87

Formula in cell C2 (copied down to C11)
=IF(COUNT(A$2:A$21)>=B2,SMALL(A$2:A$21,B2),"&quot

The golf average is AVERAGE(C2:C11)

New scores are added at the bottom until the 20 score limit is reached. Afterwards, the new scores replace the old scores starting at the top again. A marker, perhaps in a color, indicate the most recent score added.

 

[DarkSun]

You should turn Pro with scores like that!

 

[JVFriederick]

Hey. I never brag about my Putt-Putt scores.