Formula to average the last 4 non-blank numerical cells of a row?

[VB Coach]

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]

The below are all ARRAY FORMULAS:

For sporadic values in A1:J1

K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1: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(((A1: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

 

[Ron Coderre]

Typo! (sorry)

The first formula should end with 4....not 5:
=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

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

XL2002, WinXP

The below are all ARRAY FORMULAS:

For sporadic values in A1:J1

K1:
=SUMPRODUCT(ISNUMBER(MATCH(COLUMN(A1:J1),LARGE(((A1: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(((A1: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

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

 

[T. Valko]

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

 

[Harlan Grove]

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.