Running Average

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
 
Try...

E35:

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

Hope this helps!
 
=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
 
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
 
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!
 
Back
Top