CODEWITHSUNDEEP
vbaexcel
Josh
Josh

Reputation: 109

Re-writing an AVERAGEIFS statement into a STDEV statement

I am looking to rewrite my averageifs statement into a STDEV statement. I currently have an average if statement which looks for the current name "N" within the type "M", and finds the same type and name within columns "A" and "B", and will average the results "C" for those rows.

=AVERAGEIFS(C:C,A:A,M4,B:B,N4)
=AVERAGEIFS(C:C,A:A,M5,B:B,N5)
=AVERAGEIFS(C:C,A:A,M6,B:B,N6)
etc...

I would like to do the same with STDEV, however the inputs requirements are different as STDEVIFS, doesn't exist.

=STDEV(BG:BG,C:C,BL4,K:K,BM4) will give the dev of all the columns. How could I fix this to be the same as my averageifs statement, but for STDEV.

  A       B          C                     M        N            O
  x       x          x                     x        x            x
  x       x          x                     x        x            x
Type    Name       Mass                   Type     Name       AVG Mass
Fruit   Apple        3                    Fruit    Apple       4.25
Veggie  Tomato       5                    Fruit    Orange      6.5
Veggie  Lettuce      1                    Veggie   Tomato      6.333
Veggie  Tomato       7                    Veggie   Lettuce     2.3333
Fruit   Orange       6                    Fruit    Watermelon  5.5
Fruit   Apple        5                    Veggie   Watermelon  4
Fruit   Watermelon   5
Veggie  Watermelon   3
Fruit   Apple        3
Fruit   Apple        6
Veggie  Watermelon   5
Fruit   Watermelon   6
Fruit   Orange       7
Veggie  Lettuce      3
Veggie  Lettuce      3
Veggie  Tomato       7

x = non included info

Upvotes: 0

Views: 859

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You can use an IF function to return either the appropriate value, or a Boolean FALSE which will be ignored. So, given your data sample, and example might be: (entered with ctrl+shift+enter as an array formula)

=STDEV(IF((Type=M5)*(Name=N5),Mass))
  • Type is the Named Range in Column A
  • Name is the Named Range in Column B
  • Mass is the Named Range in Column C
  • M5 contains the Type you are looking for
  • N5 contains the Name you are looking for

Note: You can use whole (or partial) column arguments instead of Named Ranges in the formulas above -- it would just be less efficient.

enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

You would us an array form of STDEV with an IF() inside:

=STDEV(IF(($A$4:$A$19=M4)*($B$4:$B$19=N4),$C$4:$C$19))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of enter when exiting edit mode. If done correctly the Excel will put {} around the formula.

Array formulas are different than normal formula in that they will calculate every thing in the referenced range and therefore full column references should not be used.

You can do as Ron stated in his answer and name the ranges to limit the references. Or you can use a table which will do the same or just limit the range as I have done here.

enter image description here

Upvotes: 1

Related Questions