3

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
4
  • how is this different from previous task?
    – user0
    Jun 10, 2019 at 2:08
  • Since the formula you provided previously basically broke down the process into two separate parts, the first that created and grouped unique IDs and provided the AVERAGE, and a second function to determine the STDEV, 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 the STDEV formula applied to each.
    – Corey
    Jun 10, 2019 at 2:49
  • in that case, the output would need to be: AverageLength|AverageWidth|LengthSD|WidthSD not AverageLength|LengthSD|AverageWidth|WidthSD
    – user0
    Jun 10, 2019 at 2:54
  • Agreed. Initially I was thinking of the process as a single step, when I had to explain how the problem was different I realized that the real issue is trying to produce the two AVERAGE columns for the different variables. I updated the question to match that output.
    – Corey
    Jun 10, 2019 at 3:15

1 Answer 1

1

F2:

=ARRAYFORMULA(QUERY({A2:A&B2:B, C2:D}, 
 "select Col1,avg(Col2),avg(Col3) 
  where Col1 is not null 
  group by Col1 
  label avg(Col2)'',avg(Col3)''", 0))


I2 and drag down:

={STDEV(FILTER(C:C, A:A&B:B=F2)), STDEV(FILTER(D:D, A:A&B:B=F2))}

5
  • For some reason Col2 seems to be coming out fine, but Col3 seems to be missing a ton of entries. In my actual dataset, the two variables are separated by additional columns, could this be causing an issue? Basically, instead of =ARRAYFORMULA(QUERY({A2:A&B2:B, C2:D} I have =ARRAYFORMULA(QUERY({A2:A&B2:B, C2:E} written. Could that be the issue? If so, what is the best way to deal with it?
    – Corey
    Jun 10, 2019 at 3:50
  • 1
    yes it could. change Col3 to Col4
    – user0
    Jun 10, 2019 at 4:10
  • I think that's the issue. Just to confirm that I'm pulling from the correct column, the "Col" numbering is based on my QUERY range, correct? So in the example above, entries in Col1 are my unique IDs, Col2 is C and Col3 is D. If my range was from C to F, I would just continue to count out from C (C=Col2, D=Col3, E=Col4, and F=Col5), correct? Probably basic stuff, but being able to visualize variations in the process helps a lot to understand what is going on.
    – Corey
    Jun 10, 2019 at 4:49
  • 1
    yes, that is correct
    – user0
    Jun 10, 2019 at 4:52
  • I know this answer is old, but I don't seem to understand what the ARRAYFORMULA function is doing in this formula: =ARRAYFORMULA(QUERY({A2:A&B2:B, C2:D} . Doesn't the QUERY produce the exact same result, without the ARRAYFORMULA being needed as part of the formula? I'm just trying to improve my understanding of "compound" formulas, where I get easily confused...
    – kirkg13
    May 15, 2020 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.