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 .


[TABLE="width: 500"]
<tbody>[TR]
[TD]S[/TD]
[TD]N[/TD]
[TD]M[/TD]
[TD]A[/TD]
[TD]Std[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]First[/TD]
[TD]101[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]First[/TD]
[TD]105[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]First[/TD]
[TD]109[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]First[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]First[/TD]
[TD]209[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First[/TD]
[TD]220[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First[/TD]
[TD]221[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First[/TD]
[TD]290[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First[/TD]
[TD]293[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First[/TD]
[TD]291[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Second[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Second[/TD]
[TD]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Second[/TD]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Second[/TD]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Second[/TD]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Second[/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Second[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Second[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Second[/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Second[/TD]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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