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
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