converting hourly values into daily values

[Reza]

Hi,

I have a column with the measured temperature every hour of the year(8760
values). I wolud like to convert these to the mean value for each day (365
values).

Does anyone know a simple formula/function for this purpose?

I got this formula from another thread, however I don't know how to change
it so that my first value is calculated on B3 for the values A3:A26

B1: =AVERAGE(OFFSET($A$1,ROW()*24-24,,24))

Does anyone know how I can change this formula so that it can start on the
3rd row??


Many thanks!!

 

[Bob Phillips]

=AVERAGE(OFFSET($A$3,0,0,24))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Reza]

Hey Bob,

Thanks for the reply. Unfortunately the formula gives me the same result for
all 365 days. Any thoughts??

Thank you,
Reza

 

[Max]

Sorry, think I missed seeing your earlier posting

B1: =AVERAGE(OFFSET($A$1,ROW()*24-24,,24))

Try this revision of the above to suit your instance
In B3, copied down:
=AVERAGE(OFFSET($A$3,ROWS($1:1)*24-24,,24))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---

 

[Reza]

Genious!

Thank you it worked.

Sorry, think I missed seeing your earlier posting


Try this revision of the above to suit your instance
In B3, copied down:
=AVERAGE(OFFSET($A$3,ROWS($1:1)*24-24,,24))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---