StDev of values in column X for all rows with identical values in column Y

[KadieB]

I am trying to figure out how to have my spreadsheet calculate the standard deviation of values in one column on a range where all the values in another column are identical. 

There may be several identical values or none in column Y, and I have been working against myself for too long. I want to find the maximum variation in one parameter that can still result in the same answer. I have been using standard deviation but am open to any measure of variability, as long as the formula can flex to accommodate variable ranges based on identical answers in column Y

How can I get excel to recognize a "family" and then do something based on that range?

[#2]

@KadieB 

=STDEV.S(IF(F1:F25=G1:G25,F1:F25))

Is this the dynamic formula you are looking for?

[#3]

@KadieB I happen to see and focus in on this part of your post "I want to find the maximum variation in one parameter that can still result in the same answer." So I don't understand exactly what you are doing and a sample worksheet might help, but i wanted to mention that Excel has a SOLVER built in (DATA tab).

              

So maybe one of these functions could help you?

[#4]

@OliverScheurich I attached a file to better explain what I'm looking for.

I need to calculate standard deviation (or any measure of variability, really) of variable sizes of data in one column. In the attached file, you can see that for every value in column A that is identical, I found the standard deviation of that same range in column B. I am working with thousands of rows of data and 7 or more columns that I want to analyze the variation of, and doing this several times per day, so doing it manually isn't really an option for me.

[#5 · Accepted solution]

@KadieB  Your indicated you have Excel365 so you have FILTER and UNIQUE functions.  in the Attached I created a small table based on using those functions.  In Columns F&G are using regular references which means you need to adjust the range based on the amount of data.  In columns I&J I use TABLE references so as you have more or less data in the table (I defined the data to be a table) the formulas will automatically scale.  That said, the formula for std dev in both cases needs to be pulled down to include all the unique values found in the previous column.

[#6]

Thank you, I think I can make this work!

[#7]

@KadieB 

You could also use a pivot table.

A in rows area.

B in values area. Change the calculation to StDev.

[#8]

@KadieB 

Soon it will be possible to take the @mtarler solution further using Lambda helper functions.

= LET(
    STDDEVλ, LAMBDA(a,
      LET(
        sublist, FILTER(tbl[B], tbl[A]=a),
        IF(COUNT(sublist)>1, STDEV.S(sublist),0))),
   distinct, SORT(UNIQUE(tbl[A])),
   CHOOSE({1,2}, distinct, MAP(distinct, STDDEVλ)))

The first LET allows the local names 'STDDEVλ' and 'distinct' to be defined for use in the final line.  STDDEVλ takes a single value from tbl[A] and calculates the standard deviation from the corresponding sub-list.  The name 'distinct' is applied to the sorted list of values from column [A].  After the setup the final line, the MAP function feeds the distinct values into STDDEVλ one at a time.  CHOOSE is simply a device to combine the two columns so that they may be output from a single formula.