Forum Discussion
Help averaging under certain conditions.
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.
That definitely works. I'm fairly new to excel so if you could explain the formula I'd really appreciate that!
Dates and times are stored as numbers in Excel, with 1 day as unit: the date is the integer (whole number) part and the time is the fractional part.
INT(A2:A26281) returns the integer part of A2:A26281, i.e. the dates 01-Jan-2021 etc. Since you have multiple rows for each date, the result will have many duplicates. The UNIQUE function removes the duplicates, resulting in a list of unique dates.
The AVERAGEIFS function returns the average of a series of numbers if one or more conditions are met.
=AVERAGEIFS(B2:B26281, A2:A26281, ">="&D2#+TIME(12, 0, 0), A2:A26281, "<="&D2#+TIME(16, 0, 0)) returns the average of the temperatures in column B for which the date/time in column A is greater than or equal to the dates in column D (with the UNIQUE formula) plus 12 hours, and less than or equal to those dates plus 16 hours, i.e. the interval from 12:00 to 16:00 on each date. The # in D2# means the entire range that the formula in D2 spills to.