How to select the last 6 entries in range with a formula?
 

I manage a sports league with a handicap format. Each individual may not
play each week. I need to select the last 6 entries in a row or column to
enclude in my handicap formula. As my columns are headed by the date, there
are blanks. The worksheet could be converted to rows if neccesary. Any
idesa anyone?

Thanks for any help in advance,

How to select the last 6 entries in range with a formula?
 

With
A2: (a name)
B2:J2 (scores with some blank cells)

This ARRAY FORMULA averages the last 6 non-blank cells and if there are less
than 6 scores, it averages the available scores:
K2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:J2),LARGE(((B 2:J2<0)*COLUMN(B2:J2)),{1,2,3,4,5,6}),0))*B2:J2)/MIN(6,SUMPRODUCT(--(B2:J2<0)))

Note: For array formulas, hold down [Ctrl] [Shift] when you press [Enter],
instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Rack Pack" wrote:

I manage a sports league with a handicap format. Each individual may not
play each week. I need to select the last 6 entries in a row or column to
enclude in my handicap formula. As my columns are headed by the date, there
are blanks. The worksheet could be converted to rows if neccesary. Any
idesa anyone?

Thanks for any help in advance,

How to select the last 6 entries in range with a formula?
 

don't know how relevant this is, but i developed a formula for my brother's golf
league. each player's scores are in a row from columns B to T. they don't start
figuring the average until week 5. this takes the lowest 3 out of last 5 scores
and averages them


=IF(ISBLANK(E9),"",AVERAGE(SMALL(IF(OFFSET(B9:T9,0 ,MATCH(1E+300,B9:T9)-5,1,5)=0,MAX(B9:T9),OFFSET(B9:T9,0,MATCH(1E+300,B9 :T9)-5,1,5)),{1,2,3})))

--


Gary


"Rack Pack" <Rack wrote in message
...
I manage a sports league with a handicap format. Each individual may not
play each week. I need to select the last 6 entries in a row or column to
enclude in my handicap formula. As my columns are headed by the date, there
are blanks. The worksheet could be converted to rows if neccesary. Any
idesa anyone?

Thanks for any help in advance,

How to select the last 6 entries in range with a formula?
 

Gary,

Thank you. Ron's answer is more in line with my problem, but this may be
useful in the future as we have discussed dropping the high and low scores.

"Gary Keramidas" wrote:

don't know how relevant this is, but i developed a formula for my brother's golf
league. each player's scores are in a row from columns B to T. they don't start
figuring the average until week 5. this takes the lowest 3 out of last 5 scores
and averages them


=IF(ISBLANK(E9),"",AVERAGE(SMALL(IF(OFFSET(B9:T9,0 ,MATCH(1E+300,B9:T9)-5,1,5)=0,MAX(B9:T9),OFFSET(B9:T9,0,MATCH(1E+300,B9 :T9)-5,1,5)),{1,2,3})))

--


Gary


"Rack Pack" <Rack wrote in message
...
I manage a sports league with a handicap format. Each individual may not
play each week. I need to select the last 6 entries in a row or column to
enclude in my handicap formula. As my columns are headed by the date, there
are blanks. The worksheet could be converted to rows if neccesary. Any
idesa anyone?

Thanks for any help in advance,

How to select the last 6 entries in range with a formula?
 

Hi Ron,

Although I have been working with Excell and VBA for years, I am new to
ARRAY FORMULA. I will work with this and see if I can make it work for mu
application. I will try it in a macro amd may get to see how many lines I
can put in one macro. Thanks and I'll post a followup to let you know how it
works.

"Ron Coderre" wrote:

With
A2: (a name)
B2:J2 (scores with some blank cells)

This ARRAY FORMULA averages the last 6 non-blank cells and if there are less
than 6 scores, it averages the available scores:
K2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:J2),LARGE(((B 2:J2<0)*COLUMN(B2:J2)),{1,2,3,4,5,6}),0))*B2:J2)/MIN(6,SUMPRODUCT(--(B2:J2<0)))

Note: For array formulas, hold down [Ctrl] [Shift] when you press [Enter],
instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Rack Pack" wrote:

I manage a sports league with a handicap format. Each individual may not
play each week. I need to select the last 6 entries in a row or column to
enclude in my handicap formula. As my columns are headed by the date, there
are blanks. The worksheet could be converted to rows if neccesary. Any
idesa anyone?

Thanks for any help in advance,

How to select the last 6 entries in range with a formula?
 

Just tried it and it works like a charm. Now my only question is if I wanted
to change from the last 6 to last 9 for example, what would I change in the
formula. Right now I'm thinking that {1,2,3,4,5,6} and (6,SUMPRODUCT...
would be the sections to change. I don't think I have had enough coffee yet
this morning to really analyze it.

"Ron Coderre" wrote:

With
A2: (a name)
B2:J2 (scores with some blank cells)

This ARRAY FORMULA averages the last 6 non-blank cells and if there are less
than 6 scores, it averages the available scores:
K2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:J2),LARGE(((B 2:J2<0)*COLUMN(B2:J2)),{1,2,3,4,5,6}),0))*B2:J2)/MIN(6,SUMPRODUCT(--(B2:J2<0)))

Note: For array formulas, hold down [Ctrl] [Shift] when you press [Enter],
instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Rack Pack" wrote:

I manage a sports league with a handicap format. Each individual may not
play each week. I need to select the last 6 entries in a row or column to
enclude in my handicap formula. As my columns are headed by the date, there
are blanks. The worksheet could be converted to rows if neccesary. Any
idesa anyone?

Thanks for any help in advance,

How to select the last 6 entries in range with a formula?
 

Right now I'm thinking that {1,2,3,4,5,6} and (6,SUMPRODUCT...
would be the sections to change.

That's correct....those sections would look like this:
{1,2,3,4,5,6,7,8,9}
MIN(9,SUMPRODUCT

However, as the number of items you want to include increases, this
variation of the sequential list is more compact:
ROW($1:$9)..........replaces {1,2,3,4,5,6,7,8,9}

Just don't add rows above Row_1 or between rows 1 and 9.
If that's a risk, there are other techniques:
ROW(INDEX($A:$A,1):INDEX($A:$A,9))
or
ROW(INDIRECT("1:9"))

BTW.....Thanks for the feedback!
***********
Regards,
Ron

XL2002, WinXP


"Rack Pack" wrote:

Just tried it and it works like a charm. Now my only question is if I wanted
to change from the last 6 to last 9 for example, what would I change in the
formula. Right now I'm thinking that {1,2,3,4,5,6} and (6,SUMPRODUCT...
would be the sections to change. I don't think I have had enough coffee yet
this morning to really analyze it.

"Ron Coderre" wrote:

With
A2: (a name)
B2:J2 (scores with some blank cells)

This ARRAY FORMULA averages the last 6 non-blank cells and if there are less
than 6 scores, it averages the available scores:
K2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:J2),LARGE(((B 2:J2<0)*COLUMN(B2:J2)),{1,2,3,4,5,6}),0))*B2:J2)/MIN(6,SUMPRODUCT(--(B2:J2<0)))

Note: For array formulas, hold down [Ctrl] [Shift] when you press [Enter],
instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Rack Pack" wrote:

I manage a sports league with a handicap format. Each individual may not
play each week. I need to select the last 6 entries in a row or column to
enclude in my handicap formula. As my columns are headed by the date, there
are blanks. The worksheet could be converted to rows if neccesary. Any
idesa anyone?

Thanks for any help in advance,

How to select the last 6 entries in range with a formula?
 

Thanks for the help. I have already rewritten the formula for 50 players on
a 20 week schedule and this will save me much work in the future. And now I
have another tool to work with. Again, thanks.

"Ron Coderre" wrote:

Right now I'm thinking that {1,2,3,4,5,6} and (6,SUMPRODUCT...
would be the sections to change.

That's correct....those sections would look like this:
{1,2,3,4,5,6,7,8,9}
MIN(9,SUMPRODUCT

However, as the number of items you want to include increases, this
variation of the sequential list is more compact:
ROW($1:$9)..........replaces {1,2,3,4,5,6,7,8,9}

Just don't add rows above Row_1 or between rows 1 and 9.
If that's a risk, there are other techniques:
ROW(INDEX($A:$A,1):INDEX($A:$A,9))
or
ROW(INDIRECT("1:9"))

BTW.....Thanks for the feedback!
***********
Regards,
Ron

XL2002, WinXP


"Rack Pack" wrote:

Just tried it and it works like a charm. Now my only question is if I wanted
to change from the last 6 to last 9 for example, what would I change in the
formula. Right now I'm thinking that {1,2,3,4,5,6} and (6,SUMPRODUCT...
would be the sections to change. I don't think I have had enough coffee yet
this morning to really analyze it.

"Ron Coderre" wrote:

With
A2: (a name)
B2:J2 (scores with some blank cells)

This ARRAY FORMULA averages the last 6 non-blank cells and if there are less
than 6 scores, it averages the available scores:
K2:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(B2:J2),LARGE(((B 2:J2<0)*COLUMN(B2:J2)),{1,2,3,4,5,6}),0))*B2:J2)/MIN(6,SUMPRODUCT(--(B2:J2<0)))

Note: For array formulas, hold down [Ctrl] [Shift] when you press [Enter],
instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Rack Pack" wrote:

I manage a sports league with a handicap format. Each individual may not
play each week. I need to select the last 6 entries in a row or column to
enclude in my handicap formula. As my columns are headed by the date, there
are blanks. The worksheet could be converted to rows if neccesary. Any
idesa anyone?

Thanks for any help in advance,