Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic reporting rolling averages | Excel Worksheet Functions | |||
rolling | Excel Worksheet Functions | |||
Need help creating 3-month rolling averages... | Excel Discussion (Misc queries) | |||
Rolling 3 mth Average | Excel Worksheet Functions | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions |