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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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(((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
 
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


Ron Coderre said:
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


VB Coach said:
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).
Click to expand...
Click to expand...
 
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
 
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
Click to expand...
....

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.
 
Back
Top