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
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
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
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.
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))))
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.
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.
Last edited by Glenn Kennedy; 03-28-2017 at 07:10 AM.
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))))
Thank you so much, Glenn. Your formulas work really well (:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks