Average within a Rolling Year

[farnkay]

Hi,

I was hoping somebody may be able to help me out with a Spreadsheet I am trying to create, I have already made the Spreadsheet however at the moment I am having to go into it everyday and update manually. I was wondering if there was a calculation / formula that might be able to do what I need automatically.

I need to work out the average of some numbers, within a year from todays date, e.g. todays date is 17/05/11 so I need to find the average of all of the ratios between 17/05/2010 - 17/05/2011. Is there a way the Spreadsheet can update the date automatically and work this out?

For example -
I have a ratio in cells - M10, M16, M21, M25, M31 (These are from 17/05/2010 till todays date 17/05/2011).
To find the average of the numbers in the above cells, it needs to be divded by the number in cell - B34.

So tomorrow, it would be from 18/05/2010 till 18/05/2011 - How can I make Excel work this out automatically?

Any help you can give would be greatly appreciated!

Thanks

 

[Aladin Akyurek]

Hi,

I was hoping somebody may be able to help me out with a Spreadsheet I am trying to create, I have already made the Spreadsheet however at the moment I am having to go into it everyday and update manually. I was wondering if there was a calculation / formula that might be able to do what I need automatically.

I need to work out the average of some numbers, within a year from todays date, e.g. todays date is 17/05/11 so I need to find the average of all of the ratios between 17/05/2010 - 17/05/2011. Is there a way the Spreadsheet can update the date automatically and work this out?

For example -
I have a ratio in cells - M10, M16, M21, M25, M31 (These are from 17/05/2010 till todays date 17/05/2011).
To find the average of the numbers in the above cells, it needs to be divded by the number in cell - B34.

So tomorrow, it would be from 18/05/2010 till 18/05/2011 - How can I make Excel work this out automatically?

Any help you can give would be greatly appreciated!

Thanks

What is the range housing the dates?
What is the range housing the values you want to average?

 

[farnkay]

The dates I have are as followed -

15/07/2010
28/07/2010
22/09/2010
24/11/2010
04/12/2010
22/01/2011
10/03/2011
12/04/2011
11/05/2011

The Ratios (Numbers) I have are as followed -

0, 1, 1.5, 0, 0

Hope this helps.
Thanks

 

[Aladin Akyurek]

The dates I have are as followed -

15/07/2010
28/07/2010
22/09/2010
24/11/2010
04/12/2010
22/01/2011
10/03/2011
12/04/2011
11/05/2011

The Ratios (Numbers) I have are as followed -

0, 1, 1.5, 0, 0

Hope this helps.
Thanks

I'll assume A2:A100 for dates and B2:B100 for values to average...

Control+shift+enter, not just enter:

=AVERAGE(
    IF(A2:A100>=EDATE(TODAY(),-12),
    IF(A2:A100<=TODAY(),
      B2:B100)))

On Excel 2007 or later:

=AVERAGEIFS(
     B2:B100,
     A2:A100,">="&EDATE(TODAY(),-12),
     A2:A100,"<="&TODAY())

 

[farnkay]

Thanks for your help on this, that formula has worked for me!
Many Thanks

 

[Aladin Akyurek]

Thanks for your help on this, that formula has worked for me!
Many Thanks

You are welcome. Thanks for providing feedback.