golf handicap 4 of last 5 scores

G

Guest

I would like to set up a Excel spreadsheet for golf handicaps where it will
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. The scores would start in column d. The following is an
example of scores with an underscore being a blank:
45,50,42,43,46,37,45,45,46,44
41,43,42,_,41,_,_,47,42,39
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.
 
G

Guest

I forgot to add, I would like it to do an average of the 4 lowest of the last
5 scores.
 
B

Biff

Hi!

Assumptions:

Column A holds players names starting in A2.

Row 1 holds the 18 weekly dates the rounds are played in the range B1:S1

The scores are in the range B2:S2.

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(COUNT(B2:S2)=0,"",IF(COUNT(B2:S2)<5,AVERAGE(B2:S2),AVERAGE(SMALL(S2:INDEX(B2:S2,LARGE(IF(B2:S2<>"",COLUMN(B2:S2)-1),5)),{1,2,3,4}))))

If there are less than 5 scores the average will be for the number of scores
that are entered. If no scores are entered the formula will return blank.

Biff
 
G

Guest

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. Thanks, Biff.
 
B

Biff

You're pushing your luck!

What would be the rules? There's a lot of complications to do this.

Right now, you get the avg of the lowest 4 from the last 5. If there are
only 5 scores then you can't drop the last score and go back to get 4/5.

Define the rules and I'll see if I can figure it out!

Biff
 
G

Guest

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 Biff, I appreaciate all
the help.
 
G

Guest

I'm not sure I was entirely clear.
Here is more example
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.
8 scores entered, 41,42,40,39,43,44,38,41 It would see the 40,39,43,44,38,
throw out the 44 and average the 40,39,43,38.
Continue with this series.
I don't know if all of this can be done in one formula or at all. Thanks
again, Biff.
 
B

Biff

Ok, got it.

If you're still following this thread let me know. I put together a sample
file and if you're still "here" I'll post a link to the file.

This was *extremely* complicated to try and do in a single formula. I ended
up using helpers.

Biff
 
C

Christi

Biff, would it be possible for me to get a copy of this link, I'm dropping
every seventh score for my golfers and am having difficult with the formula.
 
T

T. Valko

That's a really old post. I'm sure I don't have that sample file anymore. I
delete them after a week or 2.

Explain what you're wanting to do and be *very specific and include all the
necessary details*.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Golf handicap using 4 of last 5 scores 2
Another golf handicap question 5
Averages - Golf League Scoring 12
Help With A Golf League 4
Golf averages 2
Golf best 2 out of 4 scores 7
Finding Average of last 5 entries 11
Golf Handicap 3

Top