+ Reply to Thread
Results 1 to 13 of 13

Standard deviation based on multiple criteria in a separate column

  1. #1
    Registered User
    Join Date
    01-19-2012
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    18

    Standard deviation based on multiple criteria in a separate column

    So, I'm trying to do some varying calculations and I've gotten my average to work fine, but I need to do standard deviation based on the same criteria.

    Column A is the one I need to do the deviation on
    Column B is the criteria column, if it is X or Y, then I need to do the deviation based on that for Column A
    Then, I need to do the deviation on the remaining that isn't X or Y (This is simple enough to do if I can just get the first working)

    My average is calculated as such: =AVERAGEIFS(A:A, B:B, {"X","Y"})
    It doesn't appear that I can do STDEVP based on the same multiple criteria, like I was hoping...that or it's late, and I've been staring at spreadsheets for 12+ hours already today

    Thanks in advance!

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Standard deviation based on multiple criteria in a separate column

    Could you attached your workbook?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Standard deviation based on multiple criteria in a separate column

    if criteria is indeed X and Y or similar sequence of letters from ascii code try such (a bit long, but shall work) array formula:
    Please Login or Register  to view this content.
    in one cell and copy it right.
    te "magic 87" is just
    Please Login or Register  to view this content.
    if you want to copy it down (not right) substirute COLUMN with ROW.
    Best Regards,

    Kaper

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Standard deviation based on multiple criteria in a separate column

    Quote Originally Posted by sstyre View Post
    =AVERAGEIFS(A:A, B:B, {"X","Y"})
    I don't think that works - that formula returns an array of two values, one for x and one for y, but if you place in a single cell you will only see the average for the first value, x in this case

    Try an array formula like this:

    =AVERAGE(IF(A1:A10={"x","y"},B1:B10))

    confirmed with CTRL+SHIFT+ENTER

    adjust ranges as require, preferably not whole columns

    You can use that exact same syntax for other functions, so for standard deviation just replace AVERAGE with STDEV
    Audere est facere

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Standard deviation based on multiple criteria in a separate column

    Hi daddylonglegs,

    It ( =AVERAGEIFS(A:A, B:B, {"X","Y"}) ) shall work and works - see attachment.

    You just need to select two cells (not sure vertical or horizontal - depends on what separators are in english version of excel for separating columns and what for separating rows) write the formula and CSE commit. You will have one array formula for both cells.
    Attached Files Attached Files

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Standard deviation based on multiple criteria in a separate column

    Hello Kaper,

    Yes, I know you can put that formula in a two cell range.....but I'm assuming that sstyre wants a single result, the average of column A where column B is "x" or "y". You can't produce that single result with AVERAGEIFS so I'm suggesting that an array formula will do that....and the same syntax can give you standard deviation for the same criteria

  7. #7
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Standard deviation based on multiple criteria in a separate column

    Hi,
    Honestly - I've been sure that you know, but expected, that you expect sstyre does not ... ;-)
    OK it starts to be twisted too much.
    Let's wait until Bow starts to flow - it's almost 6:00 there.

  8. #8
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Standard deviation based on multiple criteria in a separate column

    Let's hope sstyre is not getting the Bow water out of his basement. We need his sample file.
    Click on the star if you think I helped you

  9. #9
    Registered User
    Join Date
    01-19-2012
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Standard deviation based on multiple criteria in a separate column

    Thanks all for the input so far. I hadn't included my file initially as it contains some very sensitive client information, and just didn't have the time or energy last night to pull it out to make a sample otherwise. However, not the case now, and file is attached, with some attempts at what's been provided here.

    @daddylonglegs, I want the average of column A if column B is X or Y as a single result, you are correct. The AVERAGEIFS was indeed returning the incorrect value, I hadn't done the simple calc last night to confirm, however I get a #DIV/0 error based on what you provided (Note that I changed your column's around, as I had them opposite to what your formula stated). So, then I run into the STDEVP causing the same #DIV/0.

    Time for a coffee before I settle in for a long day of spreadsheets again!
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Standard deviation based on multiple criteria in a separate column

    in EUC/RECYCLE (H11) put
    Please Login or Register  to view this content.
    and then Alt-Shift-Enter to get "{"

  11. #11
    Registered User
    Join Date
    01-19-2012
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Standard deviation based on multiple criteria in a separate column

    Quote Originally Posted by adyteo View Post
    in EUC/RECYCLE (H11) put
    Please Login or Register  to view this content.
    and then Alt-Shift-Enter to get "{"
    I had been using ALT+SHIFT+ENTER, but it wasn't doing me any good, however saved, closed and reopened, and now it does, for H11...maybe it was the coffee This also fixed E11 for the average.

    Now, I just need to fix my OTHER calc's in E12 and H12. I found the obvious error, as I had it setup to be =AVERAGE(IF(B2:B291={"<>EUC","<>RECYCLE"},A2:A291)), however, that should actually be =AVERAGE(IF(B2:B291<>{"EUC","RECYCLE"},A2:A291)). Now, the only problem is, it's returning a different value than what it should...not by a lot, but by a bit. E12 should average out to 13.92 but this formula averages it out to 13.78. H12 should be 6.25, but it's coming out with 6.65...the overall averages, versus the average of all except for EUC and RECYCLE.

    I can fix it by simply changing the formula to be =AVERAGE(IF(B2:B291={"DATA","MAINT","NET","SVC","SW"},A2:A291)), however, if there's changes down the road that add something to that column, then I need to modify it too. Time to play with the <> to get it working.

    Thanks!

  12. #12
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Standard deviation based on multiple criteria in a separate column

    Sorry, it was Ctrl-Shift-Enter
    for H12 I got: 6.46

  13. #13
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Standard deviation based on multiple criteria in a separate column

    When you are using <> for multiple items your syntax needs to change - for excluding 2 items you can use this method:

    =AVERAGE(IF(B2:B291<>"EUC",IF(B2:B291<>"RECYCLE",A2:A291)))

    or this also works and is better for a longer list of exclusions

    =AVERAGE(IF(ISNA(MATCH(B2:B291,{"EUC","RECYCLE"},0)),A2:A291))

    either way confirm with CTRL+SHIFT+ENTER

    Those methods can both be used for STDEVP too but for average only you can also use AVERAGEIFS when excluding multiple values

    =AVERAGEIFS(A2:A291,B2:B291,"<>EUC",B2:B291,"<>RECYCLE")
    Last edited by daddylonglegs; 03-19-2014 at 12:01 PM.

+ 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 Using Multiple Criteria
    By T86157 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-19-2014, 09:57 PM
  2. Calculate standard deviation for column based on column heading
    By mattadler22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 04:25 PM
  3. Standard Deviation of a column.
    By jebckr in forum Excel General
    Replies: 2
    Last Post: 02-02-2010, 01:44 AM
  4. [SOLVED] how do you find the SD(standard deviation ) for the multiple reg
    By Basem Salman in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. [SOLVED] how do you find the SD(standard deviation ) for the multiple reg
    By Basem Salman in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

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