2

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
1

={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

  • For the Stdev filtering portions, does each unique ID need to be entered manually? The example only runs from E2 to E7, so copying duplicating the line and editing in new row number isn't too difficult, but using the QUERY formula you provided on my actual data set results in 104 unique IDs, and the list will be growing. I can edit line by line, but again, if there is a simpler and less error prone way to do it, I'd appreciate it. – Corey Jun 6 at 3:40
  • you can use stdev individually and drag down the formula: i.stack.imgur.com/BfKue.png - =IFERROR(STDEV(FILTER(C:C, A:A&B:B=D3))) – user0 Jun 6 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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