Thread: calculate hourly data

Hi friends,



I have meteorology data in 1 minutes temporal resolution for one year. I need calculate hourly average data. There is no problem to calculate averages. But, It is difficult to get a summary table according to date and hours. You can see the raw data and the summary table which I want to have in the attachment file.



Thanks

You could try this method....

Change the time ranges in H2 down so that each cell shows a single time - the start of the 1 hour period, i.e. H2 should be 00:00, H3 01:00, H4 02:00 etc.

Then in I2 use this formula

=AVERAGE(IF($A$2:$A$3000=LOOKUP(10^10,$G$2:$G2),IF($B$2:$B$3000>=MOD($H2,1), IF($B$2:$B$3000<MOD($H2,1)+"1:00",C$2:C$3000))))

This is an array formula which must be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar.

Format cell as number with 2 decimal places and then drag formula across to J2 and down as far as required.

Note: looks like you have some "rogue" data in column D, e.g. D72 = 9170001. You probably need to fix this first otherwise your results won't be accurate.

You could make the formula ignore "unusually high values" e.g. this averages excluding values over 150, adjust as required

=AVERAGE(IF($A$2:$A$3000=LOOKUP(10^10,$G$2:$G2),IF($B$2:$B$3000>=MOD($H2,1), IF($B$2:$B$3000<MOD($H2,1)+"1:00",IF(C$2:C$3000<150,C$2:C$3000)))))