Running Average

[Mike]

Hello People,

I have a worksheet set up with weather data for May 2005 and it is setup
like this:
A is the date, B is the high temp, C is the low temp; D is the average temp
and E is the normal temp.
What I want to do (among other stats) is to keep a running normal temp in
E35, which is the data in E1:E5 based on
today's date in column A.
So on May 2 E35 would have the value of 52.5 (=average(E1:E2)
on May 3 E35 would have the value of 52.7 (=avearge(E1:E3)
on May 4 E35 would have the value of 52.8 (=average(E1:E4)
on May 5 E35 would have the value of 53.0 (=average(E1:E5)
A B C D E
1 May 1 05 59 45 52 52
2 May 2 05 57 39 48 53
3 May 3 05 54 40 47 53
4 May 4 05 57 38 48 53
5 May 5 05 58 38 48 54

and so on....

I need help writing a formula to get the running normal temp as each day
changes.
Thank you in advance,
Mike

 

[Domenic]

Try...

E35:

=AVERAGE(E1:INDEX(E:E,MATCH(9.99999999999999E+307,E1:E34)))

Hope this helps!

 

[Bob Umlas]

=average(indirect("E1:E"&day(date())))
if today is 5/25, then day(date()) gives 25, formula becomes
=average(indirect("E1:E25")) which is effectively =average(E1:E25)

HTH
Bob Umlas
Excel MVP

 

[Mike]

thanks guys for your quick response, but I'm still having a problem.
in Bob's formula it says that the formula contains an error and the curser
going in between the () of the date. When I enter just the
=average(indirect("E1:E25)) it works so the problem lies in the original
formula =average(indirect("E:E"&day(date())). Wouldn't i have to tell it
where to find the date (in column A?).

In Domenic's after enterting the formula it gives me the total average for
all the days listed.
thanks in advance,
Mike

 

[Domenic]

If Column A contains true date values...

=AVERAGE(E1:INDEX(E1:E31,MATCH(TODAY(),A1:A31,0)))

If Column A contains dates as text values, in the format June 1 05...

=AVERAGE(E1:INDEX(E1:E31,MATCH(TEXT(TODAY(),"mmmm d yy"),A1:A31,0)))

If the dates are text values and are abbreviated to three letters, such
as Jun 1 05, change 'mmmm d yy" to "mmm d yy".

Hope this helps!

 

[Mike]

Thanx Domenic, I was using true dates values, it works great!!

Mike