12 Month Average

[Guest]

I calculate an average in cell G19 that includes the lastest 12 months
values. Each month I need to update the formula to exclude the first of the
last 12 months and include the new month. Currently I am doing this manually
each month. Is there a way to automatically update the formula to include
only the last 12 months in the average when new data is entered into a new
cell?
Thanks for any suggestions.

 

[Roger Govier]

Hi

Post your existing formula.
What cells hold the data that you wish to average?

 

[Guest]

I calculate an average in cell G19 that includes the lastest 12 months
values. Each month I need to update the formula to exclude the first of the
last 12 months and include the new month. Currently I am doing this manually
each month. Is there a way to automatically update the formula to include
only the last 12 months in the average when new data is entered into a new
cell?

Suppose your initial data is in column E (e.g. E8:E19). Then put the
following formula into G19 and copy down through as many cells as you want:

=if(E19="", "", average(E8:E19))

When you copy down to G20 (e.g), the formula will be changed to:

=if(E20="", "", average(E9:E20))

When you enter data into E20, G20 will automatically show the average of the
12 cells ending with E20.

 

[Guest]

Roger -
The formula as requested is
=average(F14:F16,G5:G13)
Thanks,
Dewayne

 

[Roger Govier]

Hi Dewayne

So, are you saying that next month, you want (F15:F17,G5:G13), or
(F15:F16,G5:G14) or (F14:F16,G6:G14)
Can you be a little more explicit as to what each range of cells holds,
and how you want the sample for your average to move?

 

[Guest]

Roger -
The next month I would like the range to average (F15:F16,G5:G14), the
following month (F16,G5:G15) and so on.
The range of cells holds numbers such as 12.33, 11.52 etc. which I input for
each month as we progress through the year.
Hope this helps.
Thanks again for your help.

 

[Roger Govier]

Hi Dewayne

I feel as though I am having to tease each little piece of information
from you one step at a time.
Could you provide the broader picture.
Why are the monthly cells in 2 different columns?
What is the full range of cells that can hold data in column F and
column G?
Does it start at F5:F16 for the first 12 months, then go on to G5:G16
for the next 12 months, and will it go on to H5:H16 for the following 12
months?

If so, then the following should work

=AVERAGE(
OFFSET($F$16,0,0,-(12-COUNT($G$5:$G$16)),1),
OFFSET($G$16,0,0,-(12-COUNT($H$5:$H$16)),1),
$H$5:$H$16)

 

[Guest]

Hi Roger -
Sorry! Let me try to broaden the picture for you to help you if I can.
Column B lists all 12 months January -December in rows 5 - 16.
Column C is 2002 sales vaules for each month (row 5-16 with yearly average
in row 17.
Column D is 2003 sales vaules for each month (row 5-16 with yearly average
in row 17).
Column E is 2004 sales vaules for each month (row 5-16 with yearly average
in row 17).
Column F is 2005 sales vaules for each month (row 5-16 with yearly average
in row 17.
Column G is 2006 sales values in January - October (Nov & Dec will be added
in rows 15&16 when it becomes available. YTD average is in row 17.
Currently G19 is the last 12 months sales average.
I need to keep a running average of the sales values for the most recent 12
months thus the need for the forumla that transends 2 columns that will
adjust as I add sales values for the new month, each month.
Yes you are correct that the most recent 12 months starts in Column F
(2005), goes on to G (2006) and will go H as I move on to 2007. By that time
however, there will be no need to reference F any longer as it (2005) will be
out of the range of the most recent 12 months.
Per your request, the full range of cells that can hold data in F & G is
F5:G16.
Don't know if this helps. Sorry I have been so narrow in my discription. I
do appreciate your help, time & effort!
Thanks again,
Dewayne

 

[Roger Govier]

Hi Dewayne

Then my guess was almost correct.
The formula I gave you will work for columns F, G and H i.e for the
whole of 2005, 2006 and 2007 provided the cells in the months that have
not yet been completed are empty.
The formula is counting how many cells have values in each column, and
deducting that value from 12.
If then takes that number of rows before row 16, plus any rows that have
been completed in the next column.

You should be able to adjust it for future years by changing the
appropriate column references.

 

[Guest]

Roger -
Thank you for all the help and your time.
Dewayne

 

[Roger Govier]

Hi

You're very welcome. Thanks for the feedback. Glad to know it worked for
you.