converting hour values to day mean values

[Baffe]

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?

/ Baffe

 

[Max]

Assume 24 hourly values start in A1 down
In B1: =AVERAGE(OFFSET($A$1,ROW()*24-24,,24))
Copy down to B365. B1 returns the average for A1:A24, B2 returns it for
A25:A48, etc

 

[Baffe]

Thanks a lot, it worked!

Note that by using this formula the original values have to start in the
first row!
"Max" skrev:

 

[Max]

Thanks a lot, it worked!

Welcome, glad to hear that.

Note that by using this formula
the original values have to start in the first row!

It can easily adapted to suit

For eg, if data starts in A2 down,
just use this in the starting cell, say in B2:
=AVERAGE(OFFSET($A$2,ROWS($1:1)*24-24,,24))
then copy down as before

The 2 changes required are:
A2 is now the anchor, and,
ROWS($1:1) replaces ROW()
as the start cell incrementer

 

[Reza]

Hello Max,

My first value is on row 3, how can I adjsut the formula to still work, i.e.
B1 would return the average of a3:a26??

Thank you,

 

[Reza]

sorry i mean B3, would return average of a3:a26.

Many thanks,
Reza

 

[Max]

As responded in your other 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))
---------------

You probably missed this clarification
in my last response to the original poster in Apr 08

For eg, if data starts in A2 down,
just use this in the starting cell, say in B2:
=AVERAGE(OFFSET($A$2,ROWS($1:1)*24-24,,24))
then copy down as before

The 2 changes required are:
A2 is now the anchor, and,
ROWS($1:1) replaces ROW()
as the start cell incrementer
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---