Forum Discussion
Solved
Help averaging under certain conditions.
I have calculated the "feels like" temperature for every hour of every day for 3 years (its just a temperature value in degF). I need to find a way to, for each day, find the average temperature in the afternoon (hours 12-16, including 12 and 16). This means each temperature must stay attached to its corresponding date and hour. I'm totally stuck. The dates are formatted as 01-Jan-21 00:00:00
Let's say the dates/times are in A2:A26281 and the temperatures in B2:B26281.
In for example D2 enter the formula
=UNIQUE(INT(A2:A26281))
and in E2
=AVERAGEIFS(B2:B26281, A2:A26281, ">="&D2#+TIME(12, 0, 0), A2:A26281, "<="&D2#+TIME(16, 0, 0))
Both formulas will spill to as many rows as needed.
Let's say the dates/times are in A2:A26281 and the temperatures in B2:B26281.
In for example D2 enter the formula
=UNIQUE(INT(A2:A26281))
and in E2
=AVERAGEIFS(B2:B26281, A2:A26281, ">="&D2#+TIME(12, 0, 0), A2:A26281, "<="&D2#+TIME(16, 0, 0))
Both formulas will spill to as many rows as needed.
For the first part of your suggestion, its giving me values in the 44,000s. Is that correct?
Yes. format that column as a date.
