I am tracking weekly scores in a table and want to be able to calculate a
4-entry trailing average. Not every cell will have a value, so the last 4
entries might be spread over more than 4 cells in the row. For example,
tracking player point totals over a period of weeks, a player might be sick
one week and thus not have points that week, so the cell would be blank (as
opposed to zero).

The below are all ARRAY FORMULAS:

For sporadic values in A1:J1

K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4}),0 ))*A1:J1)/5

Or, if there may be less than 4 items and the available items are to be
averaged
K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4}),0 ))*A1:J1)/MIN(4,SUMPRODUCT(--(A1:J1<0)))

or

=AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<"",COLUMN( A1:J1)),MIN(COUNT(A1:J1),4))))

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

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

XL2002, WinXP


"VB Coach" wrote:

I am tracking weekly scores in a table and want to be able to calculate a
4-entry trailing average. Not every cell will have a value, so the last 4
entries might be spread over more than 4 cells in the row. For example,
tracking player point totals over a period of weeks, a player might be sick
one week and thus not have points that week, so the cell would be blank (as
opposed to zero).

Typo! (sorry)

The first formula should end with 4....not 5:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4}),0 ))*A1:J1)/4

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

The below are all ARRAY FORMULAS:

For sporadic values in A1:J1

K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4}),0 ))*A1:J1)/5

Or, if there may be less than 4 items and the available items are to be
averaged
K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A 1:J1<0)*COLUMN(A1:J1))+((A1:J1=0)*0),{1,2,3,4}),0 ))*A1:J1)/MIN(4,SUMPRODUCT(--(A1:J1<0)))

or

=AVERAGE(J1:INDEX(A1:J1,LARGE(IF(A1:J1<"",COLUMN( A1:J1)),MIN(COUNT(A1:J1),4))))

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

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

XL2002, WinXP


"VB Coach" wrote:

I am tracking weekly scores in a table and want to be able to calculate a
4-entry trailing average. Not every cell will have a value, so the last 4
entries might be spread over more than 4 cells in the row. For example,
tracking player point totals over a period of weeks, a player might be sick
one week and thus not have points that week, so the cell would be blank (as
opposed to zero).

Try one of these. Both formulas are array formulas and need to be entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER):

This one will average the last 4 entries in row 1 (A1:IV1). If there are not
at least 4 entries to average the formula returns an error:

=AVERAGE(IF(COLUMN(1:1)=LARGE(IF(1:1,COLUMN(1:1)) ,4),IF(1:1,1:1)))

This version will average the last 4 entries in row 1 (A1:IV1). If there are
not at least 4 entries to average the formula will average the last n
entries up to 4. If there are *no* numbers to average the formula returns an
error.

=AVERAGE(IF(COLUMN(1:1)=LARGE(IF(1:1,COLUMN(1:1)) ,MIN(COUNTIF(1:1,"0"),4)),IF(1:1,1:1)))

Biff

"VB Coach" <VB wrote in message
...
I am tracking weekly scores in a table and want to be able to calculate a
4-entry trailing average. Not every cell will have a value, so the last 4
entries might be spread over more than 4 cells in the row. For example,
tracking player point totals over a period of weeks, a player might be
sick
one week and thus not have points that week, so the cell would be blank
(as
opposed to zero).

Ron Coderre wrote...
....
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE((( A1:J1<0)*COLUMN(A1:J1))
+((A1:J1=0)*0),{1,2,3,4}),0))*A1:J1)/4
....

As long as A1:J1 doesn't contain any error values, (A1:J1=0)*0 is
ALWAYS {0,0,0,0,0,0,0,0,0,0}. No point including it in this formula.

Good point, Harlan.....as always, your insight is appreciated.

***********
Regards,
Ron

XL2002, WinXP


"Harlan Grove" wrote:

Ron Coderre wrote...
....
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE((( A1:J1<0)*COLUMN(A1:J1))
+((A1:J1=0)*0),{1,2,3,4}),0))*A1:J1)/4
....

As long as A1:J1 doesn't contain any error values, (A1:J1=0)*0 is
ALWAYS {0,0,0,0,0,0,0,0,0,0}. No point including it in this formula.