Finding average of values within the hour, over a sampling period

[Corsa88]

Hi guys, hope you are doing well!

I have a set of data with 2 parameters -> 1) timestamp (containing date and time) 2) PSI (which is an air quality index)

Timestamp format is : dd/m/yyyy h:mm:ss

The problem I am facing currently is that I need to find an average PSI within an hour. The timestamp is essentially a sampling rate of 1 second. Previously, I was inserting a table and manual sorting it by the day, then by the hour and finally using the =AVERAGE() function to obtain the average PSI for a particular hour.

At hand, I realised I have a large series (a year worth) of data over sampling period of 24 hour/day @ 1 second interval and this would take me eons to find the average PSI for each hour, for each day through the entire year, at this rate.

As such, I was wondering if anyone has any advice to compute the average PSI for the hour on a daily basis, spanning multiple days?

For your reference, I have attached the excel table below containing typical data and format that I am processing currently. Essentially, the full set of data would go on second by second 24 hours a day for 365 days in a year.

timestamp <tbody> </tbody>	PSI <tbody> </tbody>
21/7/2015 3:40:01	75
21/7/2015 3:40:02	88
21/7/2015 3:40:03	90
21/7/2015 3:40:04	84
21/7/2015 3:40:05	61
21/7/2015 18:40:01	78
21/7/2015 18:40:02	74
21/7/2015 18:40:03	83
21/7/2015 18:40:04	85
21/7/2015 18:40:05	97
22/7/2015 3:40:28	84
22/7/2015 3:40:29	54
22/7/2015 3:40:30	68
22/7/2015 3:40:31	88
22/7/2015 3:40:32	76
22/7/2015 18:40:14	71
22/7/2015 18:40:15	79
22/7/2015 18:40:16	89
22/7/2015 18:40:17	77

<tbody>
</tbody>

Would really appreciate any advice from you guys. Thank you in advance for any assistance rendered.

Regards
Corse

 

[LanceLance]

If you can set up a sample Start Stop Table

Start Time	21/07/2015	22/07/2015	23/07/2015	24/07/2015	25/07/2015
Stop Time	22/07/2015	23/07/2015	24/07/2015	25/07/2015	26/07/2015

<colgroup><col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2624;" width="82"> <col style="width: 79pt; mso-width-source: userset; mso-width-alt: 3360;" width="105"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2528;" span="4" width="79"> <tbody>
</tbody>

Can use a "Averageifs" statment

=AVERAGEIFS(PSI list,Time stamp list,">+"&B2,Time stamp list,"<+"&B3)

B2 = Start Time
B3 = Stop time

 

[steve the fish]

Make yourself a table of the dates/times you want to average then use AVERAGEIFS. Something like this:

=AVERAGEIFS(Sheet2!B:B,Sheet2!A:A,">="&A2,Sheet2!A:A,"<"&A3)

where sheet2 is the one where your data is held. In this case your PSI in column B, your timestamp in column A and your newly created table having its date/times in column A. It may error is no data is available for a particular hour producing a #DIV/0! error.

 

[Corsa88]

Make yourself a table of the dates/times you want to average then use AVERAGEIFS. Something like this:

=AVERAGEIFS(Sheet2!B:B,Sheet2!A:A,">="&A2,Sheet2!A:A,"<"&A3)

where sheet2 is the one where your data is held. In this case your PSI in column B, your timestamp in column A and your newly created table having its date/times in column A. It may error is no data is available for a particular hour producing a #DIV/0! error.

Thanks guys for the response. Ok, so in this case, I will churn out a table that has a 1 hour interval for each day and then through the entire year? if there is no data, i will then delete them? how can i select those no data #DIV/0 error?

 

[steve the fish]

Easiest way is to sort by the divide by 0 column. Delete them. Then sort by the time/date column.

 

[LanceLance]

Can just add another criteria to Averageifs statment.

=AVERAGEIFS(Sheet2!B:B,Sheet2!A:A,">="&A2,Sheet2!A:A,"<="&A3,Sheet2!A:A,">="&0)

 

[Corsa88]

Can just add another criteria to Averageifs statment.

=AVERAGEIFS(Sheet2!B:B,Sheet2!A:A,">="&A2,Sheet2!A:A,"<="&A3,Sheet2!A:A,">="&0)

Thanks Lance, would it work on your formula as well? =AVERAGEIFS(PSI list,Time stamp list,">+"&B2,Time stamp list,"<+"&B3, Time stamp list,">="&0)

 

[LanceLance]

Yes... apologies formula should be

=AVERAGEIFS(PSI list,Time stamp list,">="&B2,Time stamp list,"<="&B3, Time stamp list,">="&0)

In Call A1 type: Interval
In Call A2 type: start date and time
In Call A3 type: start date and time

Interval (days)	1
Start Date and Time	21/09/2014 00:00
Stop Date and Time

<colgroup><col style="width: 122pt; mso-width-source: userset; mso-width-alt: 5961;" width="163"> <col style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;" width="145"> <tbody>
</tbody>

in cell B1 type 1 (this is in days). if you need it in hours type =1/24
In Cel B2 type starting date
In Cell B3 Type "=B2+$B$1"

In Cell C2 Type "=B3"
In Cell C3 Type "=C2+$B$1

Interval (days)	0.041666667
Start Date and Time	21/09/2014 00:00	21/09/2014 02:00
Stop Date and Time	21/09/2014 01:00	21/09/2014 03:00

<colgroup><col style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;" width="130"> <col style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;" width="145"> <col style="width: 131pt; mso-width-source: userset; mso-width-alt: 6400;" width="175"> <tbody>
</tbody>

You Should Now be able to drag rows/column C2:C3 accross screen to copy to each cell

in Cell B4 type your formula:

=AVERAGEIFS(PSI list,Time stamp list,">="&B2,Time stamp list,"<="&B3, Time stamp list,">="&0)

and drag this to left also to copy.

You will still gwet a #DIV/0 effor ir time period is not in your "Time Stamp List"

This Can be resolved by using an if / ISERROR function

=if(ISERROR(AVERAGEIFS(PSI list,Time stamp list,">="&B2,Time stamp list,"<="&B3, Time stamp list,">="&0)),"",AVERAGEIFS(PSI list,Time stamp list,">="&B2,Time stamp list,"<="&B3, Time stamp list,">="&0))

Interval (days)	0.041666667
Start Date and Time	21/07/2015 00:00	21/07/2015 01:00	21/07/2015 02:00	21/07/2015 03:00
Stop Date and Time	21/07/2015 01:00	21/07/2015 02:00	21/07/2015 03:00	21/07/2015 04:00
Average	#DIV/0!	#DIV/0!	#DIV/0!	79.6

<colgroup><col style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;" width="130"> <col style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;" width="145"> <col style="width: 131pt; mso-width-source: userset; mso-width-alt: 6400;" width="175"> <col style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;" span="2" width="111"> <tbody>
</tbody>

Interval (days)	0.041666667
Start Date and Time	21/07/2015 00:00	`=B3
Stop Date and Time	`=B2+$B$1	`=C2+$B$1
Average	`=IF(ISERROR(AVERAGEIFS($B$12:$B$30,$A$12:$A$30,">="&B2,$A$12:$A$30,"<="&B3, $A$12:$A$30,">="&0)),"",AVERAGEIFS($B$12:$B$30,$A$12:$A$30,">="&B2,$A$12:$A$30,"<="&B3, $A$12:$A$30,">="&0))	`=IF(ISERROR(AVERAGEIFS($B$12:$B$30,$A$12:$A$30,">="&C2,$A$12:$A$30,"<="&C3, $A$12:$A$30,">="&0)),"",AVERAGEIFS($B$12:$B$30,$A$12:$A$30,">="&C2,$A$12:$A$30,"<="&C3, $A$12:$A$30,">="&0))

<colgroup><col style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;" width="130"> <col style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;" width="145"> <col style="width: 986pt; mso-width-source: userset; mso-width-alt: 48054;" width="1314"> <tbody>
</tbody>

 

[Corsa88]

Lance,

Thank you for the detailed explanation indeed. That certainly cleared things up for me. Now the format of data is what I desired.

Could I add a question on top of our current discussion:

I need to find running averages of the PSI for the past 15 days, meaning to say:

If we denote today as Day (X), I need to find the average PSI for days (X-15) to (X-1).

This is carried on to the next day, Day (X+1), where average PSI is now computed for days (X-14) to (X) (i.e. past 15 days). And so on, till end of data.

To this end, I attempted to use =AVERAGE(OFFSET(PSI_at_day_X, -15,0,15,0))

However based on the data seen in the original post, this was problematic, because simply doing a -15 rows from reference day does not guarantee data for the previous 15 days,due to the fine time interval. Is there any way to do this based on the time as a criteria?

 

[LanceLance]

Morning

if we take the same headings

Interval (days)	0.041666667
Start Date and Time	21/09/2014 00:00	21/09/2014 02:00
Stop Date and Time	21/09/2014 01:00	21/09/2014 03:00

<tbody>
</tbody>

B1 : Time Interval = 15
B2 : Start Date = 21/09/2015
B3 : =B3+$B$2 (For previous 15 days you will need a minus =B3 - $B$2)

This will get you a 15 day average

C2 : for next day =B3+1
C3 : C2+$B$2 (For previous 15 days you will need a minus =B3 - $B$2)

You will then be able to Drag C2 and C3 across to right to copy.

Formula for Average Calculation (in row 4)will just update to look at the new dates.