CODEWITHSUNDEEP
excelexcel-formulaaveragestandard-deviationarray-formulas
ValientProcess
ValientProcess

Reputation: 1801

Average and Standard Deviation for every hour of measurements

I have an Excel file with time logs and measurement taken at each time, the list is keeping logs for several days. I want to calculate the average and the standard deviation of the measurements for every hour.

My problem is that the amount of measurements taken each hour is different, so I can't even start.

The list looks like this:

Time            measurement
28/8/2013 16:17 3831
28/8/2013 16:36 3668
28/8/2013 16:36 3685
28/8/2013 16:36 3676
28/8/2013 17:13 3671
28/8/2013 17:36 3669
28/8/2013 17:36 3664
28/8/2013 18:36 3654
28/8/2013 18:36 3661

And I need output like this:

  Hour             Average      Stdv
28/08/2013 16:00    3715    77.64448897
28/08/2013 17:00    3668    3.605551275
28/08/2013 18:00    3657.5  4.949747468

Any ideas ?

Upvotes: 1

Views: 512

Answers (2)

user4039065
user4039065

Reputation:

An AVERAGEIFS function can readily accomplish the first task.

=AVERAGEIFS(B$2:B$10, A$2:A$10, ">="&D3, A$2:A$10, "<"&D3+TIME(1, 0, 0))

You will need an array formula for the conditions on a STDEV.S function for the second.

=STDEV.S(IF(A$2:A$10>=D3, IF(A$2:A$10<D3+TIME(1, 0, 0), B$2:B$10)))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum.

      enter image description here

If your datetimes in column A are sorted in an ascending order, this standard formula should work.

=STDEV.S(INDEX(B:B, IFERROR(MATCH(D3-TIME(0, 0, 1), A:A)+1,2)):INDEX(B:B, MATCH(D3+TIME(0, 59, 59), A:A)))

While that looks more complex, it parses the range of values from column B down to the matching values from column A using an INDEX/MATCH function pair. This should greatly reduce the calculations on a long list of datetimes. This is NOT an array formula and does NOT require Ctrl+Shift+Enter.

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Pivot the data with Time for ROWS and measurement in VALUES twice, once as Average and once as StdDev. Group your rows with selecting Years, Months, Days and Hours.

Upvotes: 1

Related Questions