Extract average based on month and reference

[puiuluipui]

Hi,

I need to extract in sheet "Raport", based on reference and month, the average sales from the previous three months.

Sheet "Sales"
reference January 2013 February 2013 March 2013 April 2013 May 2013
123456 10 20 30 40 50
789101 60 70 80 90 100


Sheet "Raport"
Reference April 2013 May 2013
123456 20 30
789101 70 80

I need in sheet "Raport" to display for reference 123456 and April 2013, the average sales from the previous three months. (Sheet "Sales" : 10,20,30 = average "30")
For May to display 30, which is the average from February, March and April from sheet "Sales"....and so on.


Can this be done?

Thank you kindly.

[Olly]

In Raport B2, enter:

Please Login or Register  to view this content.

Copy across / down.

[[email protected]]

Attache sheet and want to know, is month will be in sequence order then possible, use AVERAGEIFS function.

Regards,
Suhas

[puiuluipui]

Hi Olly,

Thank you for the formula, it's working, except for the fact that is not extracting the average of the previous three months.
in sheet Raport, column April 2013 I need the average from Sheet Sales, January 2013-February 2013-March 2013.

Now, it's extracting the average from February 2013-March 2013- April 2013.

I need this formula to extract the previous three months. to move the average calculation to the left with one cell.

can this be done?
Thanks.

[Jul Stev]

try this maybe?

Please Login or Register  to view this content.

[puiuluipui]

Now it's perfect!

Thank you very much!