Forum Discussion

Copper Contributor

Feb 11, 2025

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 t...

excel

Formulas and Functions

HansVogelaar
Feb 11, 2025
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.

HansVogelaar

MVP

Feb 11, 2025

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.

dl12
Copper Contributor
Feb 11, 2025
For the first part of your suggestion, its giving me values in the 44,000s. Is that correct?
- HansVogelaar
  MVP
  Feb 11, 2025
  Yes. format that column as a date.
  - dl12
    Copper Contributor
    Feb 11, 2025
    That definitely works. I'm fairly new to excel so if you could explain the formula I'd really appreciate that!