I would like to be able to group rows based on unique values and then output the AVERAGE
and STDEV
values of two different variables for those groups.
Using the table below as an example, I would like to determine the AVERAGE
and STDEV
of two different variables for each unique ID and type (1a, 1b, 2a, 2b, 3a, 3b).
I received assistance on this problem earlier which combined QUERY
and ARRAYFORMULA
and it worked wonderfully, but that was only for a single variable. Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets.
Using the same formula twice adds additional unique value columns. This not only adds additional clutter, but also seems it could introduce additional errors if the unique ID columns don't match or aren't aligned. If possible, I would like to ensure that the calculations are being pulled from the same set of unique IDs.
The answer to my previous question broke down the process into two steps, the first step grouping unique IDs and providing an AVERAGE
for them, and the second step applying STDEV
to those groups. Using the table below, the intended output would produce a column for the unique IDs, and then columns for "AverageLength" "AverageWidth" "LengthSD" "WidthSD". If the new formula could provide the first three output columns, the STDEV
formula provided from the previous question could be applied to both the length and width data to create the final two output columns.
ID Type L W
1 a 5 9
1 a 7 8
1 a 9 3
1 b 2 4
1 b 4 1
1 a 6 3
1 a 9 8
1 b 8 5
1 a 1 2
2 a 5 2
2 a 2 1
2 a 4 7
2 b 6 6
2 b 7 5
2 a 8 9
3 b 1 2
3 b 7 4
3 a 5 3
3 a 6 6
3 a 8 9
3 a 9 3
3 b 4 5
3 b 6 4
3 b 3 1
AVERAGE
, and a second function to determine theSTDEV
, I guess the goal is to be able to use one formula to create two columns of averages, one for each variable (in this case length and width), which could then have theSTDEV
formula applied to each.AverageLength|AverageWidth|LengthSD|WidthSD
notAverageLength|LengthSD|AverageWidth|WidthSD
AVERAGE
columns for the different variables. I updated the question to match that output.