asked on

Excel 2010 (or 07) Standard Deviation "IFS"

Hi experts -

I'm trying to calculate a standard deviation, but I need to do it with an "IFS" tag on it like I can do with COUNTIFS/AVERAGEIFS/SUMIFS. So where I'm taking a bunch of data on a worksheet, and then just filtering to what I want to calculate based on 3 filter parameters.

I can't seem to find an Excel function that allows this. Is there one in there somewhere?

If not, could you help me out with the syntax for such a cool operation?

Thanks!

SOLUTION

dlmille

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

barry houdini

Hello Dave,

I don't think you want to use multiplication (*) in the formula. Doing that creates zeroes for the rows that don't meet the criterion and the zeroes will skew the STDEV result. I suggest this small but significant amendment

=STDEV(IF($B$2:$B$101=$E2,$A$2:$A$101))

still confirmed with CTRL+SHIFT+ENTER

That way for rows which don't equal E2 the IF function returns FALSE and STDEV ignores such "logical values".

regards, barry

rmm2001

ASKER

Hi guys -

Thanks for the fast reply. I do agree about the 0's being created for rows that don't meet the criteria - but then the only problem is the multiple criteria thing.

How would I make =STDEV(IF($B$2:$B$101=$E2,$A$2:$A$101)) accept multiple parameters?

ASKER CERTIFIED SOLUTION

barry houdini

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

dlmille

barry - thanks for the catch.

Dave

rmm2001

ASKER

I can filter by just one column and that seems to work beautifully - but when i filter by more than one, I'm getting the #DIV/0! error. Is there a way around that?

Thanks!

rmm2001

ASKER

Wait - I take that back. I wasn't in the formula bar when i CSEd (still getting used to array formulas).

It works though! Thanks!!

rmm2001

ASKER

I had no clue about array functions before. Thanks so much for the help!

Excel 2010 (or 07) Standard Deviation &quot;IFS&quot;

Excel 2010 (or 07) Standard Deviation "IFS"