Excel dynamic average

[tractorvix]

Hi all,

I hope you'll be able to help me. I've got a data set that is added to each day and I need to dynamically be able to calculate an average over a specific time period
eg:
11/05/2009 10.0
12/05/2009 10.0
13/05/2009 10.0
14/05/2009 10.0
16/05/2009 10.0
18/05/2009 5.0
19/05/2009 5.0

average for last 5 days is 8
but adding
20/05/2009 20.0

average for last 5 days is 10.

I was hoping to use an offset function but I need to dynamically reference the starting point, so if I wanted to find the average for the 5 days prior to the 18/05/2009 (so output of 10) I could.

Am I making any sense??!!

I'm sure there some reference / index /column type formula which will enable me to find the starting point, but I just can't seem to get my head around it!

Thanks for your help

 

[SkipVought]

Hi,

Just in case you're wondering, today is the May 27. So five days ago is the 22nd. So none of your test data would qualify.

Anyhow...
[tt]
=sumproduct((YourDateRange>TODAY()-5)*(YourSumRange))/sumproduct((YourDateRange>TODAY()-5)*(1))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TonyJollans]

if you have your dates in Column A and your values in column B, and your end date in cell C1, then this should give you the average of the five entries in the list ending with the one of the date:

[blue][tt]=AVERAGE(OFFSET(A1,MATCH(C1,A:A)-4,1,5,1))[/tt][/blue]

It assumes (a) the desired date is in the list and (b) that there are four prior entries. If that isn't the case it will need some error trapping.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[TonyJollans]

Actually, it won't, but this should:

[blue][tt]=AVERAGE(OFFSET(A1,MATCH(C1,A:A)-[/tt][red][tt]5[/tt][/red][tt],1,5,1))[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

and if its the last five days in the range...
[tt]
=sumproduct((YourDateRange>MAX(YourDateRange)-5)*(YourSumRange))/sumproduct((YourDateRange>MAX(YourDateRange)-5)*(1))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

Please correct me if am missing something, BUT assuming that the dates are in column A and the data in Column B, starting with cell C5 (or cellC6..if that's what you want), wouldn't [Red]=Average(B1:B5)[/Red] work just as well? And then copy down as far you like?

Another Victim of the recession