Calculating Sample Standard Deviation

[devinda0007]

Dear Experts,
I hope I can get some help here. I have an excel file with hundreds of random values in a column (Column B, starting from Row2). What I am trying to achieve is to calculate standard deviation of every 10 consecutive rows (Example: Row#2 to Row#11, Row#3 to Row#12, Row#4 to Row#13 etc...) and output the standard deviation value in Column C (Example: Standard deviation for B2:B11 will be on Cell C2, Standard Deviation for B3:B12 will be on Cell C3 etc... And output the minimum standard deviation among all the standard deviation values into Cell E1 and get the corresponding starting and ending positions into Cells E2 and E3 respectively. This is to identify the range of minimum standard deviation among the large amount of values. Example of what I am highlighting above is shown below;
(Yellow highlighted is the minimum standard deviation)
Note that this is just an example with only 30 rows. The standard worksheet has values span over 1000 rows. So ideally, the calculation needs to happen until the last 10-rows standard deviation, which in this example is (B22:B31).

Any help, advice is immensely appreciated..

Many Thanks
Dev

 

[6StringJazzer]

This should work but it's not possible to test this without your file, or at least data. You can't test a picture.

E2:
=INDEX(A:A,MATCH(E1,C:C,0))

E3:
=INDEX(A:A,MATCH(E1,C:C,0)) + 9

Assumes numbers in column A are strictly ascending incremented by 1.

 

[Eric W]

Welcome to the MrExcel forum!

As Jeff said, it's very tedious to retype all your data from a picture. You can copy/paste some data from your spreadsheet, or ideally use the XL2BB add-in provided here. See the link in my signature or the reply box. It's very easy to use. You are MUCH more likely to get help if you make it easier for the helpers here. Here's an example:

Book1
	A	B	C	D	E
1	No	Value	Standard Deviation for 10 consecutive rows	Minimum Standard Dviation fo 10 consecutive points=	0.11858424
2	1	116	0.151964908	Starting "No"=	11
3	2	116.07	0.154358889	Ending "No"=	20
4	3	116.14	0.144622113
5	4	116.03	0.140775629
6	5	115.78	0.145922354
7	6	115.86	0.141110674
8	7	115.91	0.140669036
9	8	115.62	0.157197682
10	9	115.9	0.122315621
11	10	115.85	0.120756872
12	11	115.78	0.118584241
13	12	115.97	0.126513065
14	13	116.12	0.125879131
15	14	116.07	0.151602551
16	15	115.89	0.155452744
17	16	115.88	0.140590027
18	17	116.12	0.154933678
19	18	116.07	0.157144661
20	19	116.01	0.156776557
21	20	116.11	0.173384608
22	21	116.31	0.176726405
23	22	116.15
24	23	116.34
25	24	116.21
26	25	116.01
27	26	115.81
28	27	116.03
29	28	116.09
30	29	115.86
31	30	116.2
Sheet2

Cell Formulas
Range	Formula
E1	E1	=MIN(C:C)
E2	E2	=MATCH(E1,C2:C100,0)
E3	E3	=E2+9
C2:C31	C2	=IF(COUNT(B2:B11)=10,STDEV.S(B2:B11),"")

 

[devinda0007]

Thank you very much Jeff and Eric. And apologize for not making it easier. Honestly, I was looking for a way to upload the excel file to the forum but I could not locate an "Attach" button. Hence resorted to typing in the details and put a screen capture. Thank you very much for your guidance on how to resolve my issue and also, how to do a post correctly in the forum. I will definitely use the XL2BB add-in moving forward.

Many thanks for your advise and help in this matter.

Best Regards
Dev.

 

[Eric W]

Glad we could help!