how to find the mean, depending on dates

[abrunstad]

hello

Please see the document included.
I want to make a mean of every day (temperarures).
There are temperature registrations every haf hour.


And instead of doing this manually (40 000 rows!!), is there any way to
do this automatically?

[daddylonglegs]

To get an average in the first row of each day you can use this "array formula" in C3 copied down

=IF(DAY(A2)<>DAY(A3),AVERAGE(IF(INT(A3:A200)=INT(A3),B3:B200)),"")

confirmed with CTRL+SHIFT+ENTER

[shg]

If you data was actually consistent about being sampled every half hour, this would be easier (for your computer).

Someplace (say starting V3) make a list of dates from 2006-0903 to 2009-0213 (893 rows). Then in D3, this array formula:

=AVERAGE(IF(INT($A$3:$A$40684) = C3, $B$3:$B$40684) )

Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

Then copy down.

[abrunstad]

great!
Would you please insert it into the excel sheet and repost it?
I do not have an english version, and do not know the translation for the formula..

However, exel automatically translates :p

thanks!
-andreas

[shg]

Why don't you try it yourself?

[daddylonglegs]

just showing 1000 rows.....

[abrunstad]

Thanks guys, I got the rest of it to work!