I have an excel document with values for every hour for every day of a year. In total there are 365*24=8760 values.

I would like to get the average value at every hour, (etc at 00:00, 01:00, 02:00..) I was thinking maybe to sum every 24th value and then divide it by 365 but I don't know how to sum every 24th value.

Does anyone know how to do that?

Thanks for any help!

CodePudding user response：

The clearer way of doing it is using a column to get the hour of each data point and adding a AVERAGEIF formula to calculate the average of every hour.

1. I am assuming your date/time is on A2:A8761 and the values on B2:B8761.
2. On C2, add the formula =HOUR(A2) and drag it down to find the hour of every data observation (for newer excel versions you can also do =HOUR(A2:A8761) and the formula will automatically spill down). The results are numbers from 0 to 23.

Building the summary table:

3. Add a column listing the numbers 0 to 23 to represent the 24 hours. I did it on column E2:E25. Note that those are numbers, not date/time.
4. On F2, add the formula =AVERAGEIF($C$2:$C$8761,E2,$B$2:$B$8761) to calculate the average of the hours listed on column E. Then, drag it down. Finally, you will have the values you need on F2:F25.

Find bellow my result.

Please let me know if you have any question so I can improve the answer.