Finding AVERAGE based on DAY from TIMESTAMP

**smuqeet** · 07-11-2012, 12:47 PM

Hi Everyone,

I'm trying to get the daily average rainfall based on the actual day within the timestamp. I thought I could use the AVERAGEIF function, but am unable to apply it here. Could you help, please?

Thank you very much.

-S

**oeldere** · 07-11-2012, 01:25 PM

Maybe with a pivot table.

The attachment became 4 MB.

The link you find beneath, from a free dutch site.

http://www.mijnbestand.nl/Bestand-8JMS6Z3RZPWV.xls

**daddylonglegs** · 07-11-2012, 01:38 PM

If you want the average rainfall per 5 minute period within each day then list dates in E3 down and put this formula in F3

=IFERROR(AVERAGE(IF(INT(A$3:A$20000)=E3,B$3:B$20000)),"No rain")

confirm with CTRL+SHIFT+ENTER and copy down

If you don't mind using a helper column to extract the date into another cell then you can use AVERAGEIF which might be simpler, e.g. in C3 use this formula copied down

=INT(A3)

then you can use this formula for the average without needing "array entry"

=IFERROR(AVERAGEIF(C$3:C$20000,E3,B$3:B$20000),"No rain")