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!!

=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" wrote in message
...
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!!

Hey Bob,

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

Thank you,
Reza


"Bob Phillips" wrote:

=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" wrote in message
...
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!!

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

Genious!

Thank you it worked.

"Max" wrote:

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

Welcome. Do take a moment to press the "Yes" button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Reza" wrote:
Genious!
Thank you it worked.