Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel - Averaging Over Multiple Workbooks
I currently generate a monthly report which consists of a number of
bar charts overlaid with running 3 month and 'forever' averages. Each month I generate a new workbook which consists of that month's data 'dump' and 7 other sheets which have pivot tables generated from the 'dump' which I use to calculate the data required for each set of charts. Each sheet contains data for 3 charts (each chart has 80 'X' values with the 3 month and 'forever' averages overlaid). I have another workbook which has a sheet for each new month into which I copy the data from the workbooks previously described and another sheet which I use to calculate the 'forever' average: "=AVERAGE(1ST MONTH:CURRENT MONTH)" E.g. "=AVERAGE(JUN06:FEB07)" The problems I face with carrying out this task in this manner is that it is very clunky and time consuming. Each month for the 3 month running average I have to alter the formula to delete the oldest month and insert the latest. Likewise for the 'forever' average I have to replace the last current month and replace it with the latest. My questions a 1. Is there a method that I can use for calculating the 3 month running average automatically? 2. Is there a method I can use for calculating the 'forever' average from my current series of monthly workbooks to automatically include each new month thereby negating the need for the extra 'forever' average workbook? I would be grateful for any assistance anyone can offer on this (longwinded) matter. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching Multiple Excel Workbooks | Excel Discussion (Misc queries) | |||
Multiple workbooks on Taskbar in Excel 97 | Excel Discussion (Misc queries) | |||
Excel - Multiple Workbooks | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
averaging specific rows in multiple arrays | Excel Worksheet Functions |