Forum Discussion
StDev of values in column X for all rows with identical values in column Y
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.
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.