+ Reply to Thread
Results 1 to 4 of 4

Standard Deviation Using Multiple Criteria

  1. #1
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Standard Deviation Using Multiple Criteria

    So, I've come to a wall. The wall of pain. I have two columns of critical numerical data (Columns B and C), the rest of the columns are informative and categorical (Columns A, G and H). I'm trying to gain the standard deviation of multiple criteria similar to that of SUMPRODUCT() but "STDEVPRODUCT()".

    Formula for finding average of sumproduct:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    A3 is where a user can type in informative data to match the data found in columns G as part of the sumproduct function.

    So, is there a formula that can provide the standard deviation of the above formula? If so, what is the formula?

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

    Re: Standard Deviation Using Multiple Criteria

    It's not clear to me what calculation you are doing.

    For the most basic case, find the STDEV of column C when A = "x" and col B = "y" you can use an "array formula" like this

    =STDEV(IF(A2:A100="x",IF(B2:B100="y",C2:C100)))

    confirmed with CTRL+SHIFT+ENTER

    .....but you have two columns and I'm not clear how that should work, could you clarify or give a small example?
    Audere est facere

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Standard Deviation Using Multiple Criteria

    Array enter - enter using Ctrl-Shift-Enter - to get the standard deviation of values in B, based on criteria in G and A

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note that "the standard deviation of the above formula" is difficult to interpret....

  4. #4
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Standard Deviation Using Multiple Criteria

    Sorry, to elaborate:

    Data is entered vertically in the columns. Columns B and C. Columns B values will be divided by Column C values (i.e. =B17/C17) to get the average "rate" of two units. My approach on this was to sumproduct the values in Column B and divide by the sumproduct values in Column C to determine the average of the data that was entered. I was wanting to figure out if there was a similar approach to finding the standard deviation of the "rates" of Column B with respect to Column C (i.e. Column B per Column C standard deviation). I believe this would require an array within one cell in order to determine this time of data unless I must have a formula to combine the two columns within a third column, which I tried and 0/0 or empty cells will provide an error for the average and standard deviation. So maybe my approach is wrong or there are some formulas I do not know of? Does this help clarify what I am trying to accomplish? Finding the average rate between two columns, then finding the standard deviation rate between the same two columns.

+ 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. how do you find the SD(standard deviation ) for the multiple reg
    By Jerry W. Lewis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  2. 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, 02:05 AM
  3. 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, 01:05 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, 12:05 AM
  5. 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-05-2005, 10:05 PM

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