CODEWITHSUNDEEP
exceltimeaverage
user3683409
user3683409

Reputation: 43

How can i calculate the average per hour?

I need to plot my data, but just a value per hour, the problem here is sometimes I have just one value at that hour, but other times I can have 4 our 5 readings at the same hour; so How can I calculate the average per hour?

thanks!

enter image description here

Upvotes: 0

Views: 8323

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You can use a Pivot Chart.

Drag Date/Time to rows and it will probably automatically group by hours. If it doesn't you can enable that.

Then drag the Values column to Values, and select to show the Average

enter image description here

Upvotes: 0

barry houdini
barry houdini

Reputation: 46341

Assuming date/times in A2 down and values in B2 down try this formula in C2

=AVERAGEIFS(B:B,A:A,">="&FLOOR(A2,"1:00"),A:A,"<"&CEILING(A2,"1:00"))

FLOOR function will round down to the hour and CEILING will round up so this averages between 01:00 and 02:00 etc.

The above version will give you the average on every row (with repeats). If you alter it like this then the average will only appear on the last value for each hour

=IF(FLOOR(A2,"1:00")<>FLOOR(A3,"1:00"),AVERAGEIFS(B:B,A:A,">="&FLOOR(A2,"1:00"),A:A,"<"&CEILING(A2,"1:00")),"")

Upvotes: 4

Related Questions