# summarizing sensor Hourly Data to daily data

Copper Contributor

# summarizing sensor Hourly Data to daily data

I have for each daily date hourly data in the same cell, i.e., the cell contains the date and hour. I am interested in calculating the average daily value from the hourly soil moisture sensor values. How to do it in Excel. Also, if it is possible to split the cell of date and hour into two columns, date and hours?

Thank you

5 Replies

# Re: summarizing sensor Hourly Data to daily data

``=AVERAGE(IF(\$D\$3:\$D\$18=D3,\$B\$3:\$B\$18))``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

# Re: summarizing sensor Hourly Data to daily data

Or use AVERAGEIF.

=AVERAGEIF(\$D\$3:\$D\$18,D3,\$B\$3:\$B\$18)

See attached example, based on that by @OliverScheurich

For fun, I used INT instead of ROUNDDOWN and MOD instead of subtracting the date.

# Re: summarizing sensor Hourly Data to daily data

@Khalil_Ammar
Another option for averaging is PivotTable with grouping datetime as desired

# Re: summarizing sensor Hourly Data to daily data

Thank you very much@Hans Vogelaar. It worked.

# Re: summarizing sensor Hourly Data to daily data

Thank you very much, Sergie. This was very helpful.