Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling date looking 30 Days past
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling date looking 30 Days past
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 --------------- Jacob Skaria "Chris" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rolling date looking 30 Days past
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 --- "Chris" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of days past due | Excel Worksheet Functions | |||
Count No of days past due | Excel Worksheet Functions | |||
get a rolling sum between current date - 30 days | Excel Worksheet Functions | |||
Rolling Balance after 30 days | Excel Worksheet Functions | |||
flag date within a cell after 15 days have past? | Excel Worksheet Functions |