CODEWITHSUNDEEP
exceldatetimeexcel-formulaformula
Grubbmeister
Grubbmeister

Reputation: 877

Average day and night temperature in excel

I have a large dataset of temperature and humidity readings, collected every half hour for a year. I'd like to get an average of day and night temperature, but my experiments with averageifs have so far failed. This is as far as I got.

=AVERAGEIFS(Sheet1!D$7:D$17622,Sheet1!$C$7:$C$17622, ">="&$A21, Sheet1!$C$7:$C$17622,"<" &$A22)

The values in A21 and A22 are 6:00 and 18:00, formatted as h:mm.

This just returns a DIV/0! error - I don't have any zeros, so I don't know what the issue is.

I have exactly the same formula working with dates. Both formulas are drawing on the same input, entered into each cell like so:

1/02/2016  3:00:00 PM

I would be most grateful for any assistance (or if this is the wrong forum, let me know).

Upvotes: 3

Views: 2225

Answers (2)

Grubbmeister
Grubbmeister

Reputation: 877

One problem that wasn't solved was how to get night averages (day worked fine). I was also wanting the averages for each season. Here's how I fixed that with an array formula:

=AVERAGE(IF((Sheet1!C$7:C$17622>=A$31)+(Sheet1!C$7:C$17622<A$32),IF(Sheet1!$B$7:$B$17622 >=$B31, IF(Sheet1!$B$7:$B$17622<$B32,Sheet1!D$7:D$17622))))

In Sheet1, column C is Time, Column B is Date/time, and D is temperature.

And: A31 = 18:00 A32 = 6:00 (Night time)

B31 = 1/03/2016 B32 = 1/06/2016 (example range for season)

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21639

Your problems is the size of the ranges you are specifying.

From the documentation:

Syntax:

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

The AVERAGEIFS function syntax has the following arguments:

Average_range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1, criteria_range2, … Criteria_range1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, … Criteria1 is required, subsequent criteria are optional. 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range.

So, with AVERAGEIFS, SUMIFS, COUNTIFS, if the range to calculate (the fist paramater) is, for example, A1:A10, then all of criteria ranges that you specify also have to span 10 cells.

You may be incorrectly using AVERAGEIFS instead of AVERAGEIF.

  • If you have a single row of criteria and want to average one or more rows of data , use AVERAGEIF.

  • If you have multiple criteria (ie., before this time and after that time) and want to average one row of data, use AVERAGEIFS.

I don't believe you can use either for multiple criteria + multiple data rows. If that's what you're trying to do, you may need to use SUMIFS and COUNTIFS for each row and then calculate an average from those.

For example:

  • If you want to average daytime temperatures (between 06:00 and 18:00)

  • times are stored in A1:X1 like 00:00 to 23:00 (*and do not have dates attached to them, even if not 'visible')

  • temperatures are stored in columns A:X (one day per row)

  • and you want to calculate average daytime temperatures, for one day, which is stored on Row 2,

... then you could use:

=AVERAGEIFS(A2:X2,A1:X1,">="&6/24,A1:X1,"<&18/24)

Note that when taking totals based on start/end dates/times, it's generally best to use:

  • >= the Start date/time, and,

  • < the End date/time

...to prevents overlaps.

Also, when getting into more complex formulas like this, be sure to manually calculate a few rows to compare and verify that you've correctly specified the formula criteria.

More Information:

Upvotes: 2

Related Questions