I would like to set up a Excel spreadsheet for golf handicaps where it will use
the 4 lowest scores of the last 5 entries. It must be able to work even if
there are missed entries (someone didn't show up/blanks ignored). I would
like it to work if there are only three scores used at the beginning of the
season, but when there are 5 or more scores, use only the 4 lowest scores out of the last 5. I want it to average these scores. The scores would start in column d. The following is an example of scores with an underscore being a blank:
Golfer 1 45,50,42,43,46,37,45,45,46,44
Golfer 2 41,43,42,_,41,_,_,47,42,39
Golfer 3 44,43,45,54,_,45,43,40,42,_
Our season is 18 weeks long, but I don't think that should matter with this
formula. Can you help me with this problem? Thank you.
Golf Scores: Average x Lowest of Last x
-
-
-
Re: Golf scores average 4 lowest of last 5
Hello Golfnut
welcome to OzGrid!
see attached file please.
As you will notice, the solution can be put in 1 cell, but I don't recommend you to use this way unless you're handy with the Excel formulas. The result is in column O for each golfer. These are array formulas, so conclude the formula with Ctrl-Shft-Enter i.s.o. Enter. Acolades will appear around the formula to denote this.
The easier formulas (but they're identical) are below. Again, yellow marks the solution.
Good luck with it
Wigi
-
Re: Golf scores average 4 lowest of last 5
Thanks so much for the help.
-
Re: Golf scores average 4 lowest of last 5
Assuming that D2:U2 will contain your golf scores for the 18 weeks, try...
=IF(COUNT(D2:U2)=3,AVERAGE(D2:U2),IF(COUNT(D2:U2)>3,AVERAGE(SMALL(INDEX(D2:U2,LARGE(IF(D2:U2<>"",COLUMN(D2:U2)-COLUMN(D2)+1),5)):U2,{1,2,3,4})),""))
...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
-
Re: Golf scores average 4 lowest of last 5
I have one more question for you. Could you give me a formula to show the
old handicap (average). I want it basically to work the same except I
want it to ignore the last entry. That way I can show whether the handicap
went up or down from the previous week.
Let's say my scores are 1-2-3-4-5-6-1-2
Right now, the formula you made for me takes the 4-5-1-2 and averages them while ignoring the 6. What I would like to do in this new formula is pretend the 2 was never entered. I would look at the 3-4-5-6-1, throw out the 6 and average the 3-4-5-1. That way, when you look at the list of golf's
handicaps, you can look at whose handicaps went up and whose went down at a glance. I hope I made this clear to you. I know this is asking for a lot,
but I hope you can figure this out for me.
Thanks again for all the help! -
-
Re: Golf scores average 4 lowest of last 5
Try...
=AVERAGE(SMALL(INDEX(D2:U2,LARGE(IF(D2:U2<>"",COLUMN(D2:U2)-COLUMN(D2)+1),6)):INDEX(D2:U2,MATCH(9.99999999999999E+307,D2:U2)-1),{1,2,3,4}))
...confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
-
Re: Golf scores average 4 lowest of last 5
That worked great except when there were less that 5 scores entered. I don't know if it is possible, but it would be really nice if the formula would work when there are only 2 to 4 entered too (like at the beginning of the season).
Example:
2 scores entered, 41,42 It would only see the 41 and list that as the average.
3 scores entered, 41,42,40 It would only see the 41 & 42 and average those.
4 scores entered, 41,42,40,39 It would only see the 1st 3 scores and average those
5 scores entered, 41,42,40,39,43 It would only see the 1st 4 scores and average those
6 scores entered, 41,42,40,39,43,44 It would only see the 1st 5 scores, throw out the highest score (43), and average the other 4.
7 scores entered, 41,42,40,39,43,44,38 It would see the 42,40,39,43,&44, throw out the 44 and average the 42,40,39,&43.
Continue with this series.
I don't know if all of this can be done in one formula or at all.
Thanks, Domenic! -
Re: Golf scores average 4 lowest of last 5
Try...
=IF(COUNT(D2:U2)>1,AVERAGE(SMALL(INDEX(D2:U2,LARGE(IF(D2:U2<>"",COLUMN(D2:U2)-COLUMN(D2)+1),MIN(COUNT(D2:U2),6))):INDEX(D2:U2,MATCH(9.99999999999999E+307,D2:U2)-1),ROW(INDIRECT("1:"&MIN(COUNT(D2:U2)-1,4))))),"")
...confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
-
Re: Golf scores average 4 lowest of last 5
Thank you so much. It worked great. Your a genious!
-
Re: Golf scores average 4 lowest of last 5
Did you get your golf sheet working well? The reason I ask is I wrote a golf analyst program many many years ago and just rewrote the entire thing in Excel and was wondering if you would like to try it and help me debug/enhance it? No charge as I never charged for the original (it was shareware).
-
-
Re: Golf scores average 4 lowest of last 5
Golfnut1,
Here is a UDF that may work for you.
Code
Display MoreFunction LastRoundAvg(Score As Range, Rounds As Integer) Dim i As Integer Dim x As Integer Dim avgS As Long x = 0 avgS = 0 For i = Score.Cells.Count To 1 Step -1 If WorksheetFunction.CountIf(Score, ">0") < Rounds Then Rounds = WorksheetFunction.CountIf(Score, ">0") If x = Rounds Then Exit For If Score(i) > 0 Then avgS = avgS + Score(i) x = x + 1 End If Next i LastRoundAvg = avgS / x End Function
use: =LastRoundAvg(A1:A50,2) ; in this example it users the last 2 rounds. So you can chage the 2 to any number of rounds (spaces are ignored).
maybe someone can modify to throw out the high score.
Ross
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!