# STDEV with an IF not quite working (using ctl-shift-enter)

1. ## STDEV with an IF not quite working (using ctl-shift-enter)

Hello-
I think I'm close...but not quite there. I am using control-shift-enter with this function. Using Excel Mac 2011

Trying to get the stdev for a set of data using an "if" statement where, for example, the car is red and the package number is package 1 (but I would like to be able to toggle that to a variable).

=STDEV(IF(\$D\$13:\$D\$615="Red",IF(\$F\$13:\$F\$615="1",\$G\$13:\$G\$615)))

but more generally something like
=STDEV(IF(\$D\$13:\$D\$615=A1,IF(\$F\$13:\$F\$615=B1,\$G\$13:\$G\$615)))
Where A1 is the color of the car (people can type in)
And B1 is the package number (so people can type in 1 or 2 or 3....)

Thanks

2. ## Re: STDEV with an IF not quite working (using ctl-shift-enter)

Originally Posted by muchexceltolearn
...
=STDEV(IF(\$D\$13:\$D\$615="Red",IF(\$F\$13:\$F\$615="1",\$G\$13:\$G\$615)))
...
And B1 is the package number (so people can type in 1 or 2 or 3....)
Hi

What is the problem you are having? Is the result not what you expect?

Remark: I see that in your formula you are testing against the text value "1". Are the values in F13:F615 text values?

It would help if you would post a small sample, for ex. rows 13:25 so that we can test.

3. ## Re: STDEV with an IF not quite working (using ctl-shift-enter)

The calculation is returning a #N/A
Though now I see in the range \$D\$13:\$D\$615 that there are some cells with #N/A (and likewise for \$F\$13:\$F\$615 and \$G\$13:\$G\$615).
Is there a way to have the calc ignore all of the rows with #N/A when calculating the stdev?

Thanks much!

4. ## Re: STDEV with an IF not quite working (using ctl-shift-enter)

Hi

Can't you get rid of the error values when you generate/import the data?

You can just filter the error values. For ex., if the error can appear in any field in any row

=STDEV(IF(NOT(ISERROR(\$D\$13:\$D\$615&\$F\$13:\$F\$615&\$G\$13:\$G\$615)),IF(\$D\$13:\$D\$615="Red",IF(\$F\$13:\$F\$615="1",\$G\$13:\$G\$615))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

You can simplify the formula if, for ex., when there is a #N/A error value all the 3 fields in the same row have it.

5. ## Re: STDEV with an IF not quite working (using ctl-shift-enter)

Thanks lecxe. I agree those are the other options...was trying to avoid those if possible just due to how the file I have is set up. But if the only way...so it is!

6. ## Re: STDEV with an IF not quite working (using ctl-shift-enter)

STDEVIF:
``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)