calculate averages on 'sliding' scale (6 months for #1, 5 months for #2, etc.)

[jk.bleu]

I am a newbie to Excel and so need help.


Basically, we have a worksheet that has monthly data for the last few years. In our example, we have the 'final' sales data available up to December 2011 but the latest months in 2012 are 'guessed' (by the 'powers-that-be') which are entered to the worksheet. Once data has been collected for the latest month (i.e. December 2011), we calculate an 'estimate' for the next 6 months (i.e. January 2012 - June 2012).

The current method for the 'estimates' are calculated by averaging volume (in column C) for the last 6 months. However, the powers-that-be want to change it to a 'sliding' scale where

- the June 2012 (i.e. last month) would use 6 months of data (July 2011-December 2011)

- May 2012 would use 5 months of data (August 2011-December 2011)

- April 2012 would use 4 months of data (September 2011-December 2011)

- March 2012 would use 3 months of data (October 2011-December 2011)

- February 2012 would use 2 months of data (November 2011-December 2011)

- January 2012 would use 1 months of data (December 2011)


Is there any way to create a formula to automatically do this?


Thank you.

-J

 

[SirJB7]

Hi, jk.bleu!

Give a look at this file:

http://www.2shared.com/file/GqrSkzss/calculate_averages_on_sliding_.html

I have differences in the new averages: let's take F25 cell, 3 months average from 2011-07 to 2011-09, 10+40+200=250, 250/3=83 1/3, but you say 66 2/3. Which is right?

Regards!

PS: Next time consider uploading a file instead of an image. See the third green sticky post at this forums main page for details.

 

[Faseeh]

Hi Sir SirJB7,


Sorry for interruption, but can't download this file...


Thanks,

Faseeh

 

[Luke M]

Faseeh,

I'm not able to see your image or SirJB7's file, but here's one way of doing it.


Assumptions:

Data for January - December 2011 is in A2:L2.

The date of January 2012 is in M1


Formula in M2:

=AVERAGE(OFFSET($L$2,,,1,-MONTH(M1)))


Formula uses the month number to determine how many cells to include in the average.

 

[SirJB7]

Hi, jk.bleu!

Sorry, I thought 2shared was working fine. Here's again, bet duplicated, now at 4shared (should we arrive at 128shared?):

http://www.4shared.com/file/zvdH_il_/calculate_averages_on_sliding_.html

Regards!

 

[jk.bleu]

Thank you, guys!!


sorry, this has been my first post (on any forum) so i did not realize the issue with the image. As per SirJB7's suggestion, i will upload the file next time. Also in a rush to post my question, i may have made a mistake on the average's which is why the averages were probably off.


It seems that will test out both the approaches because it seems that a combination of both (using the month number and the cell address grab) may work better for me.


Take care!!

-J

 

[SirJB7]

Hi, jk.bleu!

That's the best you can do, read all suggestions, merge them or build yourself another one. Glad to help you, welcome back when needed.

Regards!