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.

Hi!

You left out the key word..........

where it will the 4 lowest scores of the last 5 entries.

Average?

Biff

"Golf.nut1" wrote in message
...
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.

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

"Golf.nut1" wrote:

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.

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4 lowest of the
last
5 scores.

"Golf.nut1" wrote:

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.

WOW, are you good. That worked like a charm. Thanks for all your help!

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4 lowest of the
last
5 scores.

"Golf.nut1" wrote:

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.

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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all your help!

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4 lowest of the
last
5 scores.

"Golf.nut1" wrote:

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.

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all your help!

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4 lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.

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.

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all your help!

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4 lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.

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.

"Golf.nut1" wrote:

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.

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all your help!

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4 lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.

Ok, let me see if I can come up with something.

Biff

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

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.

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all your
help!

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4 lowest
of
the
last
5 scores.

"Golf.nut1" wrote:

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.

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

"Biff" wrote in message
...
Ok, let me see if I can come up with something.

Biff

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

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.

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all your
help!

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4
lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.

Yes I'm still following this thread. I'll keep watching. Thanks.

"Biff" wrote:

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

"Biff" wrote in message
...
Ok, let me see if I can come up with something.

Biff

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

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.

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all your
help!

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
I forgot to add, I would like it to do an average of the 4
lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.

Here's a sample file: (the link expires in 7 days or 25 downloads)

http://s50.yousendit.com/d.aspx?id=3...N1HNW6WEUVCVRK

Biff

"Golf.nut1" wrote in message
...
Yes I'm still following this thread. I'll keep watching. Thanks.

"Biff" wrote:

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

"Biff" wrote in message
...
Ok, let me see if I can come up with something.

Biff

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

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.

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all
your
help!

"Biff" wrote:

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

"Golf.nut1" wrote in
message
...
I forgot to add, I would like it to do an average of the 4
lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.

Thanks again for all of your help!

"Biff" wrote:

Here's a sample file: (the link expires in 7 days or 25 downloads)

http://s50.yousendit.com/d.aspx?id=3...N1HNW6WEUVCVRK

Biff

"Golf.nut1" wrote in message
...
Yes I'm still following this thread. I'll keep watching. Thanks.

"Biff" wrote:

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

"Biff" wrote in message
...
Ok, let me see if I can come up with something.

Biff

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

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.

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all
your
help!

"Biff" wrote:

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

"Golf.nut1" wrote in
message
...
I forgot to add, I would like it to do an average of the 4
lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.

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.

"Biff" wrote:

Here's a sample file: (the link expires in 7 days or 25 downloads)

http://s50.yousendit.com/d.aspx?id=3...N1HNW6WEUVCVRK

Biff

"Golf.nut1" wrote in message
...
Yes I'm still following this thread. I'll keep watching. Thanks.

"Biff" wrote:

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

"Biff" wrote in message
...
Ok, let me see if I can come up with something.

Biff

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

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.

"Biff" wrote:

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

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all
your
help!

"Biff" wrote:

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

"Golf.nut1" wrote in
message
...
I forgot to add, I would like it to do an average of the 4
lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.

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*.


--
Biff
Microsoft Excel MVP


"Christi" wrote in message
...
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.

"Biff" wrote:

Here's a sample file: (the link expires in 7 days or 25 downloads)

http://s50.yousendit.com/d.aspx?id=3...N1HNW6WEUVCVRK

Biff

"Golf.nut1" wrote in message
...
Yes I'm still following this thread. I'll keep watching. Thanks.

"Biff" wrote:

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

"Biff" wrote in message
...
Ok, let me see if I can come up with something.

Biff

"Golf.nut1" wrote in message
...
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.

"Golf.nut1" wrote:

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.

"Biff" wrote:

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

"Golf.nut1" wrote in
message
...
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.

"Golf.nut1" wrote:

WOW, are you good. That worked like a charm. Thanks for all
your
help!

"Biff" wrote:

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

"Golf.nut1" wrote in
message
...
I forgot to add, I would like it to do an average of the 4
lowest of
the
last
5 scores.

"Golf.nut1" wrote:

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.