Rolling date looking 30 Days past

[Chris]

I'm logging data into a spreadsheet daily and keeping track of a rolling 30
days average and 1 std dev above the average. How do I easily create a way
to update this everyday without changing the formula every day? I have dates
on the left and numbers (25, 32, 28, etc.) next to the date. Thanks.

 

[Jacob Skaria]

If you have headers and data starts from row 2 ; in row 31 insert the formula
and copy down as required...In the below example it average the values of Col
B

=AVERAGE(INDIRECT(ADDRESS(ROW()-29,1) & ":B" & ROW(B31) ))

If this post helps click Yes

 

[Max]

Another interp ...
Assume real dates in A1 down, corresponding values in B1 down
In C1: =AVERAGE(OFFSET(INDIRECT("B"&MATCH(TODAY(),A:A,0)),,,-30))
will return the required rolling average which is dynamic to the current
date (today). It of course presumes that there's always a match found in col
A for today's date, and that there's >= 30 data rows in cols A/B to start with
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik