Calculating the Standard Deviation within a Data Set over Variable Ranges

[rwmill9716]

I have a set of data defined by dates collected in Column I and measured values in Column J. I need the average and standard deviation of that data by week. As presented, Column K identifies the week number, Column L a composite of the year with that week number, Column M the last day in that week (contained in the data set) and Column N the average value for that week. I need a formula or macro that calculates in Column N the standard deviation for that week, which would be 2.34 for the first week shown.

Thanks for your help,

Ric

Excel 2012
	I	J	K	L	M	N
2			=WEEKNUM(I4)	=YEAR(I4)&"_"&K4	=IF(K4=K5,"",I4)	=IF(M4="","", AVERAGEIF($L$4:$L$5000,L4,$J$4:$J$5000))
3	Date	Composite	Week Number		Date	Avg by day across tanks
4	01/02/11	12	2	2011_2
5	01/02/11	16	2	2011_2
6	01/04/11	13	2	2011_2
7	01/04/11	18	2	2011_2
8	01/06/11	13	2	2011_2
9	01/06/11	16	2	2011_2	01/06/11	14.67
10	01/09/11	12	3	2011_3
11	01/09/11	9	3	2011_3
12	01/11/11	11	3	2011_3
13	01/11/11	13	3	2011_3
14	01/13/11	15	3	2011_3
15	01/13/11	10	3	2011_3	01/13/11	11.67
16	01/16/11	17	4	2011_4
17	01/16/11	15	4	2011_4
18	01/18/11	15	4	2011_4
19	01/18/11	12	4	2011_4	01/18/11	14.75
20	01/23/11	22	5	2011_5
21	01/23/11	20	5	2011_5
22	01/25/11	19	5	2011_5
23	01/25/11	16	5	2011_5
24	01/27/11	18	5	2011_5
25	01/27/11	21	5	2011_5	01/27/11	19.33
26	01/30/11	19	6	2011_6
27	01/30/11	23	6	2011_6
28	02/01/11	18	6	2011_6
29	02/01/11	19	6	2011_6
30	02/03/11	19	6	2011_6
31	02/03/11	19	6	2011_6	02/03/11	19.50
32	02/06/11	20	7	2011_7
33	02/06/11	24	7	2011_7
34	02/08/11	22	7	2011_7
35	02/08/11	26	7	2011_7
36	02/10/11	19	7	2011_7
37	02/10/11	22	7	2011_7	02/10/11	22.17
Salt Tanks

 

[DRSteele]

A Pivot Table would make quick work of this task. Would you accept a PT?

 

[DRSteele]

If you don't want a PT, you could try this in O3 to calculate the StDev. Copy down as necessary.

	O
StDev

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #E2EFDA"][/TD]

</tbody>

Sheet23​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]O3[/TH]
[TD="align: left"]{=IF(M3="","",STDEV.S(IF($L$3:$L$36=L3,$J$3:$J$36)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]

 

[rwmill9716]

Thanks, DRSteele, this works great.

Ric

 

[DRSteele]

I'm glad it worked for you. You're welcome.