code for Average and Standadr deviation for certain values in excel 2010

[farhan89]

Hey ppl,

I am new to developer option or excel coding , I am writing a short code to calculate Average and Standard deviation of certain values, Since I need to update file daily so I think I need to store this code in some other excel file to run it and update the values or if I can store the code in excel and run on my file then please tell me .

S	N	M	A	Std
1	First	101
1	First	105
1	First	109
1	First	200
1	First	209
2	First	220
2	First	221
2	First	290
2	First	293
2	First	291
1	Second	30
1	Second	39
1	Second	38
1	Second	34
1	Second	35
3	Second	36
3	Second	33
3	Second	40
3	Second	42
3	Second	41

<tbody>
</tbody>

This is how my Sheet look, Each 'N' has 5 'S' it will be in increment order and max 'S' can be 25 . For each N I want to calculate average and Standard Deviation for the similar S (eg Average and Standard deviation of all 1's M then Average standard deviation of all 2's M). Can anyone give me hint to do that , Where should I start ?
The filename is always "FILE.xlsx" and Sheet name is "NEW" .

Thanks

P.S: sorry for the bad explanation

 

[steve the fish]

Putting your table in A1:E21 these formulas will work. You can then incorporate into macro if required.

For average:

=AVERAGEIFS($C$2:$C$21,$A$2:$A$21,A2,$B$2:$B$21,B2)

For standard deviation:

=STDEV(IF($A$2:$A$21=A2,IF($B$2:$B$21=B2,$C$2:$C$21)))

This one must be entered CNTL-SHIFT-ENTER

 

[Andrew Poulsom]

You could also use a pivot table:

Excel 2010
	A	B	C	D	E	F	G
1	S	N	M		Row Labels	Average of M	StdDev of M2
2	1	First	101		1
3	1	First	105		First	144.8	54.66443085
4	1	First	109		Second	35.2	3.563705936
5	1	First	200		2
6	1	First	209		First	263	38.81365739
7	2	First	220		3
8	2	First	221		Second	38.4	3.78153408
9	2	First	290
10	2	First	293
11	2	First	291
12	1	Second	30
13	1	Second	39
14	1	Second	38
15	1	Second	34
16	1	Second	35
17	3	Second	36
18	3	Second	33
19	3	Second	40
20	3	Second	42
21	3	Second	41
Sheet1

 

[farhan89]

Thanks Andrew and Steve for your quick help