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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Baffe" wrote:
I have a column with the measured temperature every hour of the year(8760
values). I would like to convert these to the mean value for each day (365
values).

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

/ Baffe

Thanks a lot, it worked!

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Baffe" wrote:
I have a column with the measured temperature every hour of the year(8760
values). I would like to convert these to the mean value for each day (365
values).

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

/ Baffe

"Baffe" wrote
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 a
A2 is now the anchor, and,
ROWS($1:1) replaces ROW()
as the start cell incrementer
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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,



"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Baffe" wrote:
I have a column with the measured temperature every hour of the year(8760
values). I would like to convert these to the mean value for each day (365
values).

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

/ Baffe

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

Many thanks,
Reza

"Reza" wrote:

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,



"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Baffe" wrote:
I have a column with the measured temperature every hour of the year(8760
values). I would like to convert these to the mean value for each day (365
values).

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

/ Baffe

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 a
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
---
"Reza" wrote in message
...
sorry i mean B3, would return average of a3:a26.

Many thanks,
Reza

"Reza" wrote:

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,