Rolling Average and Standard deviation over a range of days

[Renegade115]

Hello, My goal is to find the coefficient of variation for eight days of measurements previous so I need to find the average and standard deviation. In my spreadsheet I have the date of the measurements from oldest to newest in column A and I have the measurements in column B. What makes it difficult is that there are not measurements for every day and some days have multiple measurements.

For the average I have tried =AVERAGEIF(A$4:A4,">=" & DATE(YEAR(A4),MONTH(A4),DAY(A4)-8),B$4:B4). The problem with this is that it calculates the average over the last 8 calendar days and I need the average of the last 8 days that measurements were taken.
I have not found any solution for the standard deviation yet. I would appreciate any help.
Thanks!

Here is a small section of the data.
Time of Measurement Measurement
7/26/2017 21:39 10.3
7/26/2017 21:40 5.4
7/26/2017 21:41 11.9
7/28/2017 21:53 10.5
7/28/2017 21:53 11.4
7/28/2017 21:54 11.2
7/30/2017 23:08 10
7/30/2017 23:09 10.5
7/30/2017 23:10 10.8
8/2/2017 23:55 9.4
8/2/2017 23:56 11
8/2/2017 23:57 10.5
8/5/2017 22:48 10.9
8/5/2017 22:48 11.3
8/5/2017 22:49 11.6
8/8/2017 23:26 10.6
8/8/2017 23:27 10.7
8/8/2017 23:28 11.3
8/10/2017 21:21 9.3
8/10/2017 21:22 11
8/10/2017 21:23 11.1
8/14/2017 21:44 11.2
8/14/2017 21:45 11.7
8/14/2017 21:46 11.1
8/16/2017 23:01 11.3
8/16/2017 23:02 11.4
8/16/2017 23:03 11.6

[FDibbins]

Maybe add in a test for there being a measurement taken?
data-range, >0

[FDibbins]

My stats is REALLY rusty, but maybe some sort of ARRAY formula built into the =STDEV.S function?

[FlameRetired]

If it were me I'd use a couple of Dynamic Named Ranges (DNR).

If you aren't familiar with DNRs they resize to fit the data.

Here are two. Find them in Name Manager.

Measured_Days: is the range of all days measured.

Please Login or Register  to view this content.

Measurements: is the range of all measurements.

Please Login or Register  to view this content.

Then a formula to return the last 8 dates as a helper column (column E in attached).

Please Login or Register  to view this content.

The average of those last 8 days measurements array entered in H1.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

And array entered in I1 the STDEV.

Please Login or Register  to view this content.

[FlameRetired]

In H1 you could also use this non-array formula.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

For Average

Please Login or Register  to view this content.

For Standard deviation

Please Login or Register  to view this content.

Helper column C is used, can be hidden.

[Renegade115]

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

For Average

Please Login or Register  to view this content.

For Standard deviation

Please Login or Register  to view this content.

Helper column C is used, can be hidden.

Thanks kvsrinivasamurthy! Would there be a way to use these formulas to get different calculations for each row. For example the second to last line in the sample I shared, the average and standard deviation should not include the measurement bellow it because it did not happen yet at the time that measurement was taken. Basically I'm trying to find average and standard deviation for each new measurement using the previous 8 days of measurement.

[Renegade115]

Thanks kvsrinivasamurthy! Would there be a way to use these formulas to get different calculations for each row. For example the second to last line in the sample I shared, the average and standard deviation should not include the measurement bellow it because it did not happen yet at the time that measurement was taken. Basically I'm trying to find average and standard deviation for each new measurement using the previous 8 days of measurement.

Nevermind my last question, I think I got it. Just unlocked the second part of the ranges. The only problem now is that the entries before the last entry of 8/14/17 cannot be calculated because there are not 8 days of previous measurements. This is not a huge deal since they are the oldest entries but if I could have help to fix that it would be appreciated.

[kvsrinivasamurthy]

The present formula includes dates from 28/7/17 to 16/8/17.
Which is the exact date range required.

[Renegade115]

The present formula includes dates from 28/7/17 to 16/8/17.
Which is the exact date range required.

I'm sorry I only added a sample of what I have, I'll attach all of it. This is my first time adding an attachment to this forum so I will try again if it doesn't work. I added three columns to the spreadsheet for the average of the first, second, and third set of 8 days using AVERAGE($B$4:Bx)to compare with the averages from the formula. I also added a column with the formula you posted.

In the attachment the averages from the formula you sent have the correct value for last entry of each day I highlighted them in blue but I would like G29 and G30 to match E29 and E30 as well F32 and F33 to match G32 and G33 but those averages in between take the average from the 9 previous days (including that day). I put the ranges of values those cells use next to them to give a better idea of what I mean by the 9 days. I highlighted in purple the values that I would like to be the averages in the formula for the same row.

I hope all of this makes sense. I'm assuming the standard deviation would need the same changes so I did not include it in the spreadsheet. If this can be fixed I would greatly appreciate the help!

[kvsrinivasamurthy]

Revised formulas
In C4 then drag down

Please Login or Register  to view this content.

ARRAY formula in G4, then drag down.

Please Login or Register  to view this content.

[Renegade115]

Revised formulas
In C4 then drag down

Please Login or Register  to view this content.

ARRAY formula in G4, then drag down.

Please Login or Register  to view this content.

Thank you so much!