Averageifs - based on multiple dates average time values

**gavinp** · 06-20-2013, 02:51 PM

Hello

I have a basic temperature logger which logs outdoor temperature, this data can then be uploaded to the pc and through .csv format can be used in Excel (I use a data connection here though). I use Excel for averages, min, max, ranges etc. My data interval is every 30 mins, therefore I get 48 data sets a day. What I would now like to do is obtain an average reading at every time interval but over the month to build up monthly averages for how the temperature changes each day. This will be set up with months of the year along the top row, time from 00:00 -> 23:30 down the first column. This will read back to the raw data that syncs off the logger.

Right, my question. I think I would use an averageifs function for this but I dont know how to tell excel to only average 00:30 values and 01:00 etc. I have it set up so far like this.

=averageifs(temp_data,dates_and_time,">="&'June',dates_and_time,"<"&'July',....

Obviously June and July are cell references which I have written as so for clarity, and temp_data, dates_and_time are named ranges that automatically increase as new data arrives. How would I tell excel to then only look at time intervals?

Do I make sense?

**MarvinP** · 06-20-2013, 03:26 PM

Hi gavinp and welcome to the forum,

I think a Pivot Table is what you are looking for. Find attached a fake set of data with dates in ColA and temps in ColB. Then I did a Pivot Table and grouped the date data by day. I hope this helps you deal with your data and start learning Pivots.

**gavinp** · 06-20-2013, 03:46 PM

Thank you for your reply MarvinP, however that isn't exactly what i'm having issues with. I have already determined my average temperatures per day per month. What I am after now is for example, for the whole month of June I would like to determine the average temperature at 00:00, then 00:30 then 01:00 etc. i.e the average temperatures at those certain times per month.

**MarvinP** · 06-20-2013, 03:56 PM

Hi gavin,

How about making a helper column that has hour and minute and then group by the helper column.

OR

Perhaps using the Hour and minute in the Columns and not row.

The real answer is you need to play with the pivot table features to get exactly what you want.

**gavinp** · 06-20-2013, 04:32 PM

After a bot of trial and error I got the outcome I wanted. I used the average function with nested if's rather than the averageifs function. This is what I came up with (if you wanted to know):

=AVERAGE(IF(RAW_DATES>=E$1,IF(RAW_DATES<I$1,IF((RAW_DATES-ROUNDDOWN(RAW_DATES,0))<D26+(5/(24*60)),IF((RAW_DATES-ROUNDDOWN(RAW_DATES,0))>D26-(5/(24*60)),SHED_NORTH_T)))))

using ctrl+shift+enter

E$1 = June 13
I$1 = July 13

RAW_DATES = all logger time stamps
SHED_NORTH_T = Temp records.

The rounddown section removes the dates and leaves just the time.

note that the (5/24*60) is a five minute tolerance as I think my logger adds a few milliseconds to each timestamp.