I have been appointed the golf handicap guru for a small group (30+) of
golfers. They have been keeping the handicaps by hand. I NEEDTO LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores in a row.
After the 21st score, the oldest needs to drop by date. NEED TO AVERAGE LOW
10 OF 20 SCORES. As new players join, there will be blank cells until they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19 02/02 02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79 80
81 etc, etc

Would also like to sort row by date left (newest) to right (oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

So.....

If you're adding the newest scores to the left how are you doing that? By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated" but having
to build it to accommodate column insertions makes it really complicated!

Biff

"robert morris" wrote in message
...
I have been appointed the golf handicap guru for a small group (30+) of
golfers. They have been keeping the handicaps by hand. I NEEDTO LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores in a row.
After the 21st score, the oldest needs to drop by date. NEED TO AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank cells until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19 02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79 80
81 etc, etc

Would also like to sort row by date left (newest) to right (oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

If I can "sort" the Row, or "linked" cells (Rows 5 & 6) I could delete the
oldest date visually from the last column on the right.

Thanks for your reply, Biff

Bob Morris

"T. Valko" wrote:

So.....

If you're adding the newest scores to the left how are you doing that? By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated" but having
to build it to accommodate column insertions makes it really complicated!

Biff

"robert morris" wrote in message
...
I have been appointed the golf handicap guru for a small group (30+) of
golfers. They have been keeping the handicaps by hand. I NEEDTO LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores in a row.
After the 21st score, the oldest needs to drop by date. NEED TO AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank cells until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19 02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79 80
81 etc, etc

Would also like to sort row by date left (newest) to right (oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

Try this and see how you make out:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(COUNT($C6:$AH6)<20,"",AVERAGE(SMALL($C6:INDEX( $C6:$AH6,SMALL(IF($C6:$AH6,COLUMN($C6:$AH6)-MIN(COLUMN($C6:$AH6))+1),20)),ROW(INDIRECT("1:10") ))))

If there are less than 20 scores the formula will return a blank. It will
average the lowest 10 scores from the last 20 scores (last (newest) scores
entered from the left (sorted)).

Note: if a player misses a round it's best to leave that cell empty. Don't
enter a 0.

Biff

"robert morris" wrote in message
...
If I can "sort" the Row, or "linked" cells (Rows 5 & 6) I could delete
the
oldest date visually from the last column on the right.

Thanks for your reply, Biff

Bob Morris

"T. Valko" wrote:

So.....

If you're adding the newest scores to the left how are you doing that? By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated" but
having
to build it to accommodate column insertions makes it really complicated!

Biff

"robert morris" wrote in message
...
I have been appointed the golf handicap guru for a small group (30+) of
golfers. They have been keeping the handicaps by hand. I NEEDTO LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores in a
row.
After the 21st score, the oldest needs to drop by date. NEED TO
AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank cells until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19 02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79
80
81 etc, etc

Would also like to sort row by date left (newest) to right (oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

Biff,

The formula works beautifully except for those who have less than 20 scores.
Any ideas on that one? Just curious, why ($C6:AH6) instead of (C6:V6)?
Also, I use Column A for the Names of players. How do I link A6 & A7
together for alphabetical sorting? A6 = Name, A7 = blank.

People helping people, it's a beautiful thing!!!

Thanks so much,

Bob Morris

"T. Valko" wrote:

Try this and see how you make out:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(COUNT($C6:$AH6)<20,"",AVERAGE(SMALL($C6:INDEX( $C6:$AH6,SMALL(IF($C6:$AH6,COLUMN($C6:$AH6)-MIN(COLUMN($C6:$AH6))+1),20)),ROW(INDIRECT("1:10") ))))

If there are less than 20 scores the formula will return a blank. It will
average the lowest 10 scores from the last 20 scores (last (newest) scores
entered from the left (sorted)).

Note: if a player misses a round it's best to leave that cell empty. Don't
enter a 0.

Biff

"robert morris" wrote in message
...
If I can "sort" the Row, or "linked" cells (Rows 5 & 6) I could delete
the
oldest date visually from the last column on the right.

Thanks for your reply, Biff

Bob Morris

"T. Valko" wrote:

So.....

If you're adding the newest scores to the left how are you doing that? By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated" but
having
to build it to accommodate column insertions makes it really complicated!

Biff

"robert morris" wrote in message
...
I have been appointed the golf handicap guru for a small group (30+) of
golfers. They have been keeping the handicaps by hand. I NEEDTO LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores in a
row.
After the 21st score, the oldest needs to drop by date. NEED TO
AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank cells until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19 02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79
80
81 etc, etc

Would also like to sort row by date left (newest) to right (oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

The formula works beautifully except for those who have
less than 20 scores. Any ideas on that one?

I noted in my reply:

If there are less than 20 scores the formula will return a blank.

What do you want to happen if there are less than 20 scores?

why ($C6:AH6) instead of (C6:V6)?

AH6 was just an arbitrary end of range I used in my testing. Adjust it to
suit your layout.

How do I link A6 & A7 together for alphabetical sorting?

Not sure what you mean by that?

Biff

"robert morris" wrote in message
...
Biff,

The formula works beautifully except for those who have less than 20
scores.
Any ideas on that one? Just curious, why ($C6:AH6) instead of (C6:V6)?
Also, I use Column A for the Names of players. How do I link A6 & A7
together for alphabetical sorting? A6 = Name, A7 = blank.

People helping people, it's a beautiful thing!!!

Thanks so much,

Bob Morris

"T. Valko" wrote:

Try this and see how you make out:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=IF(COUNT($C6:$AH6)<20,"",AVERAGE(SMALL($C6:INDEX( $C6:$AH6,SMALL(IF($C6:$AH6,COLUMN($C6:$AH6)-MIN(COLUMN($C6:$AH6))+1),20)),ROW(INDIRECT("1:10") ))))

If there are less than 20 scores the formula will return a blank. It will
average the lowest 10 scores from the last 20 scores (last (newest)
scores
entered from the left (sorted)).

Note: if a player misses a round it's best to leave that cell empty.
Don't
enter a 0.

Biff

"robert morris" wrote in message
...
If I can "sort" the Row, or "linked" cells (Rows 5 & 6) I could delete
the
oldest date visually from the last column on the right.

Thanks for your reply, Biff

Bob Morris

"T. Valko" wrote:

So.....

If you're adding the newest scores to the left how are you doing that?
By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated" but
having
to build it to accommodate column insertions makes it really
complicated!

Biff

"robert morris" wrote in
message
...
I have been appointed the golf handicap guru for a small group (30+)
of
golfers. They have been keeping the handicaps by hand. I NEEDTO
LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores in
a
row.
After the 21st score, the oldest needs to drop by date. NEED TO
AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank cells
until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19
02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79
80
81 etc, etc

Would also like to sort row by date left (newest) to right (oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

"T. Valko" wrote:

The formula works beautifully except for those who have
less than 20 scores. Any ideas on that one?

I noted in my reply:

If there are less than 20 scores the formula will return a blank.

What do you want to happen if there are less than 20 scores?

I would prefer the Rows with less than 20 scores to post a result of the
scores available.

why ($C6:AH6) instead of (C6:V6)?

AH6 was just an arbitrary end of range I used in my testing. Adjust it to
suit your layout.

I suspected that was the answer.

How do I link A6 & A7 together for alphabetical sorting?

Not sure what you mean by that?

Need to "Sort" Rows A6:V6 (Name & Date Rows) and keep the relationship A7:V7
(scores) with the cells. The only reason for sorting names alphabetically is
ease of entering information.

Thanks again,

Bob Morris

Biff

"robert morris" wrote in message
...
Biff,

The formula works beautifully except for those who have less than 20
scores.
Any ideas on that one? Just curious, why ($C6:AH6) instead of (C6:V6)?
Also, I use Column A for the Names of players. How do I link A6 & A7
together for alphabetical sorting? A6 = Name, A7 = blank.

People helping people, it's a beautiful thing!!!

Thanks so much,

Bob Morris

"T. Valko" wrote:

Try this and see how you make out:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=IF(COUNT($C6:$AH6)<20,"",AVERAGE(SMALL($C6:INDEX( $C6:$AH6,SMALL(IF($C6:$AH6,COLUMN($C6:$AH6)-MIN(COLUMN($C6:$AH6))+1),20)),ROW(INDIRECT("1:10") ))))

If there are less than 20 scores the formula will return a blank. It will
average the lowest 10 scores from the last 20 scores (last (newest)
scores
entered from the left (sorted)).

Note: if a player misses a round it's best to leave that cell empty.
Don't
enter a 0.

Biff

"robert morris" wrote in message
...
If I can "sort" the Row, or "linked" cells (Rows 5 & 6) I could delete
the
oldest date visually from the last column on the right.

Thanks for your reply, Biff

Bob Morris

"T. Valko" wrote:

So.....

If you're adding the newest scores to the left how are you doing that?
By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated" but
having
to build it to accommodate column insertions makes it really
complicated!

Biff

"robert morris" wrote in
message
...
I have been appointed the golf handicap guru for a small group (30+)
of
golfers. They have been keeping the handicaps by hand. I NEEDTO
LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores in
a
row.
After the 21st score, the oldest needs to drop by date. NEED TO
AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank cells
until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19
02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79
80
81 etc, etc

Would also like to sort row by date left (newest) to right (oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

Ok.....

I think I've figured out what you're doing.

Try this much simpler formula. It's still an array formula and needs to be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER):

=IF(COUNT(C7:V7)=0,"",IF(COUNT(C7:V7)<20,AVERAGE(C 7:V7),AVERAGE(SMALL(C7:V7,ROW(INDIRECT("1:10"))))) )

If you want to sort the dates and scores so that the newest date/score is on
the left:

Select the range C6:V7
Goto the menu DataSort
Click the Options button
Select: Sort left to right
OK
Then select: sort by row6 descending
OK

Biff

"robert morris" wrote in message
...


"T. Valko" wrote:

The formula works beautifully except for those who have
less than 20 scores. Any ideas on that one?

I noted in my reply:

If there are less than 20 scores the formula will return a blank.

What do you want to happen if there are less than 20 scores?

I would prefer the Rows with less than 20 scores to post a result of the
scores available.

why ($C6:AH6) instead of (C6:V6)?

AH6 was just an arbitrary end of range I used in my testing. Adjust it to
suit your layout.

I suspected that was the answer.

How do I link A6 & A7 together for alphabetical sorting?

Not sure what you mean by that?

Need to "Sort" Rows A6:V6 (Name & Date Rows) and keep the relationship
A7:V7
(scores) with the cells. The only reason for sorting names alphabetically
is
ease of entering information.

Thanks again,

Bob Morris

Biff

"robert morris" wrote in message
...
Biff,

The formula works beautifully except for those who have less than 20
scores.
Any ideas on that one? Just curious, why ($C6:AH6) instead of (C6:V6)?
Also, I use Column A for the Names of players. How do I link A6 & A7
together for alphabetical sorting? A6 = Name, A7 = blank.

People helping people, it's a beautiful thing!!!

Thanks so much,

Bob Morris

"T. Valko" wrote:

Try this and see how you make out:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=IF(COUNT($C6:$AH6)<20,"",AVERAGE(SMALL($C6:INDEX( $C6:$AH6,SMALL(IF($C6:$AH6,COLUMN($C6:$AH6)-MIN(COLUMN($C6:$AH6))+1),20)),ROW(INDIRECT("1:10") ))))

If there are less than 20 scores the formula will return a blank. It
will
average the lowest 10 scores from the last 20 scores (last (newest)
scores
entered from the left (sorted)).

Note: if a player misses a round it's best to leave that cell empty.
Don't
enter a 0.

Biff

"robert morris" wrote in
message
...
If I can "sort" the Row, or "linked" cells (Rows 5 & 6) I could
delete
the
oldest date visually from the last column on the right.

Thanks for your reply, Biff

Bob Morris

"T. Valko" wrote:

So.....

If you're adding the newest scores to the left how are you doing
that?
By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated" but
having
to build it to accommodate column insertions makes it really
complicated!

Biff

"robert morris" wrote in
message
...
I have been appointed the golf handicap guru for a small group
(30+)
of
golfers. They have been keeping the handicaps by hand. I NEEDTO
LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores
in
a
row.
After the 21st score, the oldest needs to drop by date. NEED TO
AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank cells
until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19
02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79
80
81 etc, etc

Would also like to sort row by date left (newest) to right
(oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

Biff,

This one worked beautifully!!

Thanks a lot!!

Bob Morris


"T. Valko" wrote:

Ok.....

I think I've figured out what you're doing.

Try this much simpler formula. It's still an array formula and needs to be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER):

=IF(COUNT(C7:V7)=0,"",IF(COUNT(C7:V7)<20,AVERAGE(C 7:V7),AVERAGE(SMALL(C7:V7,ROW(INDIRECT("1:10"))))) )

If you want to sort the dates and scores so that the newest date/score is on
the left:

Select the range C6:V7
Goto the menu DataSort
Click the Options button
Select: Sort left to right
OK
Then select: sort by row6 descending
OK

Biff

"robert morris" wrote in message
...


"T. Valko" wrote:

The formula works beautifully except for those who have
less than 20 scores. Any ideas on that one?

I noted in my reply:

If there are less than 20 scores the formula will return a blank.

What do you want to happen if there are less than 20 scores?

I would prefer the Rows with less than 20 scores to post a result of the
scores available.

why ($C6:AH6) instead of (C6:V6)?

AH6 was just an arbitrary end of range I used in my testing. Adjust it to
suit your layout.

I suspected that was the answer.

How do I link A6 & A7 together for alphabetical sorting?

Not sure what you mean by that?

Need to "Sort" Rows A6:V6 (Name & Date Rows) and keep the relationship
A7:V7
(scores) with the cells. The only reason for sorting names alphabetically
is
ease of entering information.

Thanks again,

Bob Morris

Biff

"robert morris" wrote in message
...
Biff,

The formula works beautifully except for those who have less than 20
scores.
Any ideas on that one? Just curious, why ($C6:AH6) instead of (C6:V6)?
Also, I use Column A for the Names of players. How do I link A6 & A7
together for alphabetical sorting? A6 = Name, A7 = blank.

People helping people, it's a beautiful thing!!!

Thanks so much,

Bob Morris

"T. Valko" wrote:

Try this and see how you make out:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=IF(COUNT($C6:$AH6)<20,"",AVERAGE(SMALL($C6:INDEX( $C6:$AH6,SMALL(IF($C6:$AH6,COLUMN($C6:$AH6)-MIN(COLUMN($C6:$AH6))+1),20)),ROW(INDIRECT("1:10") ))))

If there are less than 20 scores the formula will return a blank. It
will
average the lowest 10 scores from the last 20 scores (last (newest)
scores
entered from the left (sorted)).

Note: if a player misses a round it's best to leave that cell empty.
Don't
enter a 0.

Biff

"robert morris" wrote in
message
...
If I can "sort" the Row, or "linked" cells (Rows 5 & 6) I could
delete
the
oldest date visually from the last column on the right.

Thanks for your reply, Biff

Bob Morris

"T. Valko" wrote:

So.....

If you're adding the newest scores to the left how are you doing
that?
By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated" but
having
to build it to accommodate column insertions makes it really
complicated!

Biff

"robert morris" wrote in
message
...
I have been appointed the golf handicap guru for a small group
(30+)
of
golfers. They have been keeping the handicaps by hand. I NEEDTO
LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20 scores
in
a
row.
After the 21st score, the oldest needs to drop by date. NEED TO
AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank cells
until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19
02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85 79
80
81 etc, etc

Would also like to sort row by date left (newest) to right
(oldest).

Would anyone help a beginner with the correct formula?

Bob Morris

You're welcome. Thanks for the feedback!

Biff

"robert morris" wrote in message
...
Biff,

This one worked beautifully!!

Thanks a lot!!

Bob Morris


"T. Valko" wrote:

Ok.....

I think I've figured out what you're doing.

Try this much simpler formula. It's still an array formula and needs to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER):

=IF(COUNT(C7:V7)=0,"",IF(COUNT(C7:V7)<20,AVERAGE(C 7:V7),AVERAGE(SMALL(C7:V7,ROW(INDIRECT("1:10"))))) )

If you want to sort the dates and scores so that the newest date/score is
on
the left:

Select the range C6:V7
Goto the menu DataSort
Click the Options button
Select: Sort left to right
OK
Then select: sort by row6 descending
OK

Biff

"robert morris" wrote in message
...


"T. Valko" wrote:

The formula works beautifully except for those who have
less than 20 scores. Any ideas on that one?

I noted in my reply:

If there are less than 20 scores the formula will return a blank.

What do you want to happen if there are less than 20 scores?

I would prefer the Rows with less than 20 scores to post a result of
the
scores available.

why ($C6:AH6) instead of (C6:V6)?

AH6 was just an arbitrary end of range I used in my testing. Adjust it
to
suit your layout.

I suspected that was the answer.

How do I link A6 & A7 together for alphabetical sorting?

Not sure what you mean by that?

Need to "Sort" Rows A6:V6 (Name & Date Rows) and keep the relationship
A7:V7
(scores) with the cells. The only reason for sorting names
alphabetically
is
ease of entering information.

Thanks again,

Bob Morris

Biff

"robert morris" wrote in
message
...
Biff,

The formula works beautifully except for those who have less than 20
scores.
Any ideas on that one? Just curious, why ($C6:AH6) instead of
(C6:V6)?
Also, I use Column A for the Names of players. How do I link A6 &
A7
together for alphabetical sorting? A6 = Name, A7 = blank.

People helping people, it's a beautiful thing!!!

Thanks so much,

Bob Morris

"T. Valko" wrote:

Try this and see how you make out:

Entered as an array using the key combination of CTRL,SHIFT,ENTER
(not
just
ENTER):

=IF(COUNT($C6:$AH6)<20,"",AVERAGE(SMALL($C6:INDEX( $C6:$AH6,SMALL(IF($C6:$AH6,COLUMN($C6:$AH6)-MIN(COLUMN($C6:$AH6))+1),20)),ROW(INDIRECT("1:10") ))))

If there are less than 20 scores the formula will return a blank.
It
will
average the lowest 10 scores from the last 20 scores (last (newest)
scores
entered from the left (sorted)).

Note: if a player misses a round it's best to leave that cell
empty.
Don't
enter a 0.

Biff

"robert morris" wrote in
message
...
If I can "sort" the Row, or "linked" cells (Rows 5 & 6) I could
delete
the
oldest date visually from the last column on the right.

Thanks for your reply, Biff

Bob Morris

"T. Valko" wrote:

So.....

If you're adding the newest scores to the left how are you doing
that?
By
inserting a new column C each time?

The formula needed to do this is already somewhat "complicated"
but
having
to build it to accommodate column insertions makes it really
complicated!

Biff

"robert morris" wrote
in
message
...
I have been appointed the golf handicap guru for a small group
(30+)
of
golfers. They have been keeping the handicaps by hand. I
NEEDTO
LINK 2
cells, C5 (date) & C6 (score) together. Keeping up to 20
scores
in
a
row.
After the 21st score, the oldest needs to drop by date. NEED
TO
AVERAGE
LOW
10 OF 20 SCORES. As new players join, there will be blank
cells
until
they
reach 20 scores.

Row Date C5 D5 E5 F5 etc until W5 i.e. 01/12 01/19
02/02
02/09
etc, etc
Row Score C6 D6 E6 F6 etc until W6 i.e. 85
79
80
81 etc, etc

Would also like to sort row by date left (newest) to right
(oldest).

Would anyone help a beginner with the correct formula?

Bob Morris