4

I have asked aboutand already got answer about averageifs excel function here. However, I was also wondering if I can get standard deviation for the same data set in new column. Here is the screenshot of sample data:

Community
  • 1
  • 1
alpha189
  • 93
  • 1
  • 8

1 Answers1

6

You can use an array formula to first filter through your rows and then apply the standard deviation to the sample. Formula would look like this:

=STDEV.S(IF((IF(C:C="alpha",1,0)*IF(D:D="S14",1,0))=1,B:B,""))

Inputted using CTRL + SHIFT + ENTER. Cheers,

As provided by @ScottCraner in the comments the much improved formula:

=STDEV.S(IF((C:C="alpha")*(D:D="S14"),B:B))
nbayly
  • 2,167
  • 2
  • 14
  • 23
  • 2
    `=STDEV.S(IF((C:C="alpha")*(D:D="S14"),B:B))` is all that is needed. – Scott Craner Dec 15 '16 at 23:09
  • Also FWIW: Make sure to limit the data references to only that of the data set to avoid unnecessary calculations. – Scott Craner Dec 15 '16 at 23:11
  • @ScottCraner I might be missing a point here but, '=STDEV.S(IF((IF(C:C=C2,1,0)*IF(D:D=D2,1,0))=1,B:B,""))' gives same value in all cells. Isn't it a better way to write, if I don't want to type othr elements in columns C and D ? – alpha189 Dec 16 '16 at 12:01
  • 1
    @alpha189 Enter the formula this way. Select only E2. Put the formula in the formula bar. Hit Ctrl-Shift-Enter instead of Enter when exiting Edit mode, Excel should put `{}` around the formula if done correctly. When you have the `{}` around the formula. Drag/copy the formula down. This is an array formula and must be entered properly. – Scott Craner Dec 16 '16 at 13:22