30 Day Moving Average Ignoring Blank Cells

[Guest]

I have a column of data covering 365 days, some of the cells have data and
some don't. I am trying to figure out how to calculate a 30 day moving
average ignoring blank cells. In other words, I want the average of the last
30 days that have a data value in the cell. The moving average may have to
look back at the last 50 to 60 days in order to get 30 days that have values
to average. I would appreciate any suggestions how to calculate this.

 

[Biff]

Hi!

What if there aren't 30 days of data to average?

Assume your values are in the range A1:A365

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(A365:INDEX(A1:A365,LARGE(IF(A1:A365<>"",ROW(A1:A365)),30)))

If there aren't 30 values this version will average all values until there
are 30 or more, then it will average the last 30.

=AVERAGE(A365:INDEX(A1:A365,LARGE(IF(A1:A365<>"",ROW(A1:A365)),MIN(COUNT(A1:A365),30))))

Biff

 

[Harlan Grove]

I have a column of data covering 365 days, some of the cells have data and
some don't. I am trying to figure out how to calculate a 30 day moving
average ignoring blank cells. In other words, I want the average of the
last
30 days that have a data value in the cell. The moving average may have to
look back at the last 50 to 60 days in order to get 30 days that have
values
to average. I would appreciate any suggestions how to calculate this.

If your data were in C5:C369, then you could calculate 30 day moving
averages using array formulas like

D5:
=IF(AND(COUNT(C$5:C5)>=30,COUNT(C5)),
AVERAGE(INDEX(C$5:C5,MATCH(COUNT(C$5:C5)-29,
MMULT(--(ROW(C$5:C5)>=TRANSPOSE(ROW(C$5:C5))),--ISNUMBER(C$5:C5)),
0)):C5),"")

Fill D5 down into D6369. This will evaluate to "" until you reach the row
with the 30th number in col C and on any row thereafter in which col C
doesn't contain a number.