+ Reply to Thread
Results 1 to 8 of 8

Standard Deviation with Lookup Criteria

  1. #1
    Registered User
    Join Date
    03-10-2017
    Location
    APAC
    MS-Off Ver
    2013
    Posts
    10

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,958

    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
    Glenn



  3. #3
    Registered User
    Join Date
    03-10-2017
    Location
    APAC
    MS-Off Ver
    2013
    Posts
    10

    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: copy to clipboard
    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.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,958

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

  5. #5
    Registered User
    Join Date
    03-10-2017
    Location
    APAC
    MS-Off Ver
    2013
    Posts
    10

    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: copy to clipboard
    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: copy to clipboard
    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: copy to clipboard
    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.
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,958

    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.
    Last edited by Glenn Kennedy; 03-28-2017 at 07:10 AM.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,958

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

  8. #8
    Registered User
    Join Date
    03-10-2017
    Location
    APAC
    MS-Off Ver
    2013
    Posts
    10

    Re: Standard Deviation with Lookup Criteria

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Standard deviation
    By Denlaws in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-05-2016, 06:29 AM
  2. [SOLVED] Standard deviation based on multiple criteria in a separate column
    By sstyre in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-19-2014, 11:59 AM
  3. Standard Deviation Using Multiple Criteria
    By T86157 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2014, 09:57 PM
  4. Standard Deviation
    By coolzero in forum Excel General
    Replies: 4
    Last Post: 10-11-2013, 07:00 PM
  5. Standard Deviation when only one value is available
    By benjamings in forum Excel General
    Replies: 2
    Last Post: 08-05-2010, 06:42 PM
  6. Standard Deviation
    By mar_bun in forum Excel General
    Replies: 1
    Last Post: 06-29-2010, 03:19 AM
  7. Standard Deviation W/O Max+Min
    By snapa in forum Excel General
    Replies: 8
    Last Post: 06-22-2009, 10:11 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1