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

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.

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!

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.

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!