Forum Discussion
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
=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.
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.
Thank you very muchHansVogelaar. It worked.
Khalil_Ammar
Another option for averaging is PivotTable with grouping datetime as desired- Thank you very much, Sergie. This was very helpful.
