Rolling Averages
I have an excel spread sheet in column A I have week commencing dates for a
whole year and column B contains the sales data for each of these weeks. I need excel to give me a continual rolling average for the latest 4 weeks entered, so that when I enter another week the first week "drops" out and a new average is calculated. I am being told that this enters into the realms of programming and nearly impossible to do! Can anyone help? Charlotte X |
Rolling Averages
This will average the last 4 numbers in column B assuming the data is
entered as a contiguous block. Numbers start in cell B2. =IF(COUNT(B:B)<4,"",AVERAGE(OFFSET(B2,COUNT(B:B)-1,,-4))) If there aren't at least 4 numbers to average the formula returns a blank cell. -- Biff Microsoft Excel MVP "Charliechoo" wrote in message ... I have an excel spread sheet in column A I have week commencing dates for a whole year and column B contains the sales data for each of these weeks. I need excel to give me a continual rolling average for the latest 4 weeks entered, so that when I enter another week the first week "drops" out and a new average is calculated. I am being told that this enters into the realms of programming and nearly impossible to do! Can anyone help? Charlotte X |
Rolling Averages
Hi Charlotte
Assuming your data starts in row 2 Put this in C5 =AVERAGE(B2:B5) and drag it down as far as needed, the cell references will update as you drag. If you want to drag it past the end of your data to allow for future input use =IF(B5="","",AVERAGE(B2:B5) HTH Martin "Charliechoo" wrote in message ... I have an excel spread sheet in column A I have week commencing dates for a whole year and column B contains the sales data for each of these weeks. I need excel to give me a continual rolling average for the latest 4 weeks entered, so that when I enter another week the first week "drops" out and a new average is calculated. I am being told that this enters into the realms of programming and nearly impossible to do! Can anyone help? Charlotte X |
All times are GMT +1. The time now is 06:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com