Averages / Floating averages

Duggiefrshmc · Apr 11th, 2002, 08:32 AM

How do I format a cell to average the last 12 entries. So that I can have a monthly average for the the last 12 months. I enter new data in the next cell for the previous month. At this time I have to change the formula every month. Is there a way to have the cell range change automaticly when I enter a new month?

Dexter · Apr 11th, 2002, 08:41 AM

What I do is set up separate sheets:
Sheet 1 contains the data entry without average formulas.

Sheet 2 contains a table that calculates the 12 month floating average.

Sheet 3 contains the finished report with the floating averages. It uses lookup formulas to populate data fields and floating averages by referencing tables 1 and 2.

Hope this helps
Dexter

Mark W. · Apr 11th, 2002, 12:16 PM

=AVERAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,A:A)-12,,12))

will average the last 12 cells in column A.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 08:32 AM	#1
Duggiefrshmc New Member Join Date: Apr 2002 Posts: 1	How do I format a cell to average the last 12 entries. So that I can have a monthly average for the the last 12 months. I enter new data in the next cell for the previous month. At this time I have to change the formula every month. Is there a way to have the cell range change automaticly when I enter a new month?

Apr 11th, 2002, 08:41 AM	#2
Dexter Board Regular Join Date: Apr 2002 Location: St. Louis Posts: 71	What I do is set up separate sheets: Sheet 1 contains the data entry without average formulas. Sheet 2 contains a table that calculates the 12 month floating average. Sheet 3 contains the finished report with the floating averages. It uses lookup formulas to populate data fields and floating averages by referencing tables 1 and 2. Hope this helps Dexter

Apr 11th, 2002, 12:16 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=AVERAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,A:A)-12,,12)) will average the last 12 cells in column A.