12 Month Average

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dewayne - 03 Nov 2006 18:53 GMT

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.

Signature

Dewayne

Reply to this Message

Roger Govier - 03 Nov 2006 19:23 GMT

Hi

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

Signature

Regards

Roger Govier

>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
[quoted text clipped - 7 lines]
> cell?
> Thanks for any suggestions.

Reply to this Message

Dewayne - 03 Nov 2006 23:14 GMT

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

Signature

Dewayne

> Hi
>
[quoted text clipped - 12 lines]
> > cell?
> > Thanks for any suggestions.

Reply to this Message

Roger Govier - 04 Nov 2006 00:19 GMT

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?

Signature

Regards

Roger Govier

> Roger -
> The formula as requested is
[quoted text clipped - 22 lines]
>> > cell?
>> > Thanks for any suggestions.

Reply to this Message

Dewayne - 05 Nov 2006 15:53 GMT

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.

Signature

Dewayne

> Hi Dewayne
>
[quoted text clipped - 29 lines]
> >> > cell?
> >> > Thanks for any suggestions.

Reply to this Message

Roger Govier - 05 Nov 2006 19:00 GMT

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)

Signature

Regards

Roger Govier

> Roger -
> The next month I would like the range to average (F15:F16,G5:G14), the
[quoted text clipped - 40 lines]
>> >> > cell?
>> >> > Thanks for any suggestions.

Reply to this Message

Dewayne - 06 Nov 2006 04:24 GMT

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

Signature

Dewayne

> Hi Dewayne
>
[quoted text clipped - 58 lines]
> >> >> > cell?
> >> >> > Thanks for any suggestions.

Reply to this Message

Roger Govier - 06 Nov 2006 16:56 GMT

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.

Signature

Regards

Roger Govier

> Hi Roger -
> Sorry! Let me try to broaden the picture for you to help you if I
[quoted text clipped - 106 lines]
>> >> >> > cell?
>> >> >> > Thanks for any suggestions.

Reply to this Message

Dewayne - 06 Nov 2006 19:27 GMT

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

Signature

Dewayne

> Hi Dewayne
>
[quoted text clipped - 120 lines]
> >> >> >> > cell?
> >> >> >> > Thanks for any suggestions.

Reply to this Message

Roger Govier - 06 Nov 2006 21:18 GMT

Hi

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

Signature

Regards

Roger Govier

> Roger -
> Thank you for all the help and your time.
[quoted text clipped - 139 lines]
>> >> >> >> > cell?
>> >> >> >> > Thanks for any suggestions.

Reply to this Message

joeu2004@hotmail.com - 03 Nov 2006 19:46 GMT

> 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.

Reply to this Message