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

#### farhan89

##### New Member
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

Last edited:

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

You could also use a pivot table:

Excel 2010
ABCDEFG
1SNMRow LabelsAverage of MStdDev of M2
21First1011
31First105First144.854.66443085
41First109Second35.23.563705936
51First2002
61First209First26338.81365739
72First2203
82First221Second38.43.78153408
92First290
102First293
112First291
121Second30
131Second39
141Second38
151Second34
161Second35
173Second36
183Second33
193Second40
203Second42
213Second41
Sheet1

Thanks Andrew and Steve for your quick help

Replies
3
Views
572
Replies
3
Views
2K
Replies
14
Views
759
Replies
4
Views
396
Replies
0
Views
567

1,203,101
Messages
6,053,531
Members
444,670
Latest member
laurenmjones1111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back