Google-sheets – Perform functions (AVERAGE, STDEV) on rows grouped by unique values in Google Sheets

filterformulasgoogle sheetsgoogle-sheets-query

I would like to be able to group rows based on unique values and then output the AVERAGE and STDEV values for those groups.

Using the table below as an example, I would like to determine the AVERAGE and STDEV for each unique ID and type (1a, 1b, 2a, 2b, 3a, 3b).

One possible solution is to create a new column and use CONCATENATE to combine the ID and Type together to create unique IDs to work from, but I'd like to try to avoid extraneous columns if possible.

I feel like this could be accomplished using some combination of the QUERY, UNIQUE, and FILTER functions, but I haven't been able to figure it out. 

1   a   5
1   a   7
1   a   9
1   b   2
1   b   4
1   a   6
1   a   9
1   b   8
1   a   1
2   a   5
2   a   2
2   a   4
2   b   6
2   b   7
2   a   8
3   b   1
3   b   7
3   a   5
3   a   6
3   a   8
3   a   9
3   b   4
3   b   6
3   b   3

Best Answer

={QUERY(A1:C, "select A,avg(C) 
               where A is not null 
               group by A 
               label avg(C)'Averages'", 0);
  QUERY(A1:C, "select B,avg(C) 
               where B is not null 
               group by B 
               label avg(C)''", 0)}

0

={"Stdev";
  STDEV(FILTER(C:C, A:A=E2));
  STDEV(FILTER(C:C, A:A=E3));
  STDEV(FILTER(C:C, A:A=E4));
  STDEV(FILTER(C:C, B:B=E5));
  STDEV(FILTER(C:C, B:B=E6))}

0

______________________________________________________________

=ARRAYFORMULA(QUERY({A1:A&B1:B, C1:C}, 
 "select Col1,avg(Col2) 
  where Col1 is not null 
  group by Col1 
  label avg(Col2)'Averages'", 0))

0

={"Stdev";
  STDEV(FILTER(C:C, A:A&B:B=E2));
  STDEV(FILTER(C:C, A:A&B:B=E3));
  STDEV(FILTER(C:C, A:A&B:B=E4));
  STDEV(FILTER(C:C, A:A&B:B=E5));
  STDEV(FILTER(C:C, A:A&B:B=E6));
  STDEV(FILTER(C:C, A:A&B:B=E7))}

0

Related Topic