converting hour values to day mean values

  • Thread starter Thread starter Baffe
  • Start date Start date
B

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
 
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
 
Thanks a lot, it worked!

Note that by using this formula the original values have to start in the
first row!
"Max" skrev:
 
Thanks a lot, it worked!
Click to expand...

Welcome, glad to hear that.
Note that by using this formula
the original values have to start in the first row!
Click to expand...

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

---
 
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,
 
As responded in your other posting ..
---------------
B1: =AVERAGE(OFFSET($A$1,ROW()*24-24,,24))
Click to expand...
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
---
 
Back
Top