30 Day Rolling Average

[Chris]

I am trying to create a formula to calculate the average of the last
30 days of data I have. I want the average to be based on the today
and average the last 30 days. In addition, I want the formula to
change to take into account what today's date is.

Any help is greatly appreciated.

 

[Myrna Larson]

Assuming the data is in row C, with no embedded blank cells:

=AVERAGE(OFFSET(INDEX($C:$C,MATCH(TODAY(),$C:$C,1)),-29,0,30,1))

If you have fewer than 29 values prior to today, the above will give and erro and requires a
more complicated formula.

 

[Nick]

I think there needs to be a slight variation in the
formula post previously...

if the dates are in Column A and data is in Column B, then
you need the formula

=AVERAGE(OFFSET(INDEX($B:$B,MATCH(TODAY(),$A:$A,0)),29,0,-
30,1))

With thanks
Nick

-----Original Message-----
Assuming the data is in row C, with no embedded blank cells:
29,0,30,1))

If you have fewer than 29 values prior to today, the

above will give and erro and requires a

more complicated formula.