# Standard Deviation with Lookup Criteria

1. ## Standard Deviation with Lookup Criteria

Hi everyone,

I am having some trouble with calculating Standard Deviations, based on Lookup Criteria.

In Column S, Standard Deviation should be calculated based on rows which have the same value as "Code X" (Cell B4 comparing with Column B).

I have attempted to use an array formula for this, but Standard Deviation is calculated using all numbers, and not based on the Lookup Criteria.

Any ideas are welcome!

Thanks (:

1.jpg  Register To Reply

2. ## Re: Standard Deviation with Lookup Criteria

Try this array formula:

=STDEV.P(IF(\$C\$4:\$P\$8>0,IF(\$B\$4:\$B\$8=\$B4,\$C\$4:\$P\$8,)))

This will return the SD of the non-zero values in the entire range (C4 to P8) ONLY if the row is a Code X. If that's what you want  Register To Reply

3. ## Re: Standard Deviation with Lookup Criteria

Hi Glenn,

Thanks for replying with a solution.

I applied your formula into the Excel file, and the following is the result:

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

Is there a way to force the values in Row 6 & 8 to become all "False"? Currently, Row 6 & 8 is returning "0" which is accounted for in StDev.P function.

Also, how would you convert the formula to make it extendable, regardless of the row range?
Eg. Making the criteria "\$B4=\$B:\$B", and the lookup rows taking the "\$C:\$P" array.  Register To Reply

4. ## Re: Standard Deviation with Lookup Criteria

Sorry about that. I have just spent half an hour staring at this bl@@dy array formula. I finally noticed the stray comma that was coercing it to count all the zeros.

=STDEV.P(IF(C\$4:\$P\$8<>0,IF(\$C\$4:\$P\$8<>"",IF(\$B\$4:\$B\$8=\$B4,\$C\$4:\$P\$8))))  Register To Reply

5. ## Re: Standard Deviation with Lookup Criteria

Once again Glenn, thanks for the time you spent on the formula.

Because StDev.P ignores text, and "" is counted as text, your very first formula (below & Picture Column T)
Formula:  `Please Login or Register  to view this content.`

can be modified to the following (below & Picture Column U): just adding "" for value if false.
Formula:  `Please Login or Register  to view this content.`

The modified first formula will give the same result as your second formula (below & Picture Column V):
Formula:  `Please Login or Register  to view this content.`

1.jpg

I've got another question:
How to coerce Cell S6 & S8 to return an empty cell when Cell B6 & B8 are empty?

Also,I've changed the Formula from the specific range \$C\$4:\$P\$8, to \$C:\$P in Column W. This is to enable lookup for Rows 9 onwards.
But this significantly slows down the system. Any ideas on how to improve this?

I've attached the Excel File with the formulas above.

Thanks.  Register To Reply

6. ## Re: Standard Deviation with Lookup Criteria

Or, most simply...

=STDEV.P(IF(\$C\$4:\$P\$8>0,IF(\$B\$4:\$B\$8=\$B4,\$C\$4:\$P\$8)))

I failed to spot the spare comma at the end of my first effort =STDEV.P(IF(\$C\$4:\$P\$8>0,IF(\$B\$4:\$B\$8=\$B4,\$C\$4:\$P\$8,)))and propagated it into my second... leading to severe brain strain
Unless you really have 1 million rows... don't use whole column references

=STDEV.P(IF(\$C\$4:\$P\$100>0,IF(\$B\$4:\$B\$100=\$B4,\$C\$4:\$P\$100))) or similar will be fine.  Register To Reply

7. ## Re: Standard Deviation with Lookup Criteria

As for your last comment, simply this:

=IF(B4="","",STDEV.P(IF(\$C\$4:\$P\$100>0,IF(\$B\$4:\$B\$100=\$B4,\$C\$4:\$P\$100))))  Register To Reply

8. ## Re: Standard Deviation with Lookup Criteria

Thank you so much, Glenn. Your formulas work really well (:  Register To Reply

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