rmm2001

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!

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

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

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**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?

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,

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
barry - thanks for the catch.

Dave

Dave

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!

Thanks!

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!!

It works though! Thanks!!

ASKER

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

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,

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