Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 788
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Number of days past due rrupp Excel Worksheet Functions 2 March 18th 09 06:36 PM
Count No of days past due Mrs H Excel Worksheet Functions 4 February 6th 09 03:39 PM
get a rolling sum between current date - 30 days dustin Excel Worksheet Functions 2 August 3rd 06 10:02 AM
Rolling Balance after 30 days excelrookie05 Excel Worksheet Functions 0 September 16th 05 02:04 PM
flag date within a cell after 15 days have past? SAUDIA Excel Worksheet Functions 2 August 19th 05 04:33 PM


All times are GMT +1. The time now is 12:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"