Search code examples
google-sheetsfilterlambdaaveragegoogle-query-language

Calculate the average of sets of cells satisfying a certain criterion (AVERAGEIF)

This is my Items sheet

enter image description here

And this is my Values sheet

enter image description here

As can be seen from the table, alfa is associated with 20 40 60 80, beta with 30 40 70 80 and gamma with 50 60 70 80.

In the Items sheet in cell B1 (next to the first item) I would like a formula (Arrayformula or alike) generating the average value for each item. In my example it should be:

alfa -> 50 (that is: (20+40+60+80)/4 = 200/4)  
beta -> 55 (that is: (30+40+70+80)/4 = 220/4)  
gamma-> 65 (that is: (50+60+70+80)/4 = 260/4)

So the final result should be:

enter image description here

This is my googlesheet: example

P.S. For simplicity's sake, I used just columns A:C for items in Values sheet. In real case I have 10 columns so I want to avoid to specify each one in the formula and instead use a range.

Solution

  • try:

    =BYROW(A1:A3, LAMBDA(x, INDEX(QUERY(SPLIT(FLATTEN(Values!A1:C10&"​"&Values!D1:D10), "​"), 
 "select avg(Col2) where Col1 = '"&x&"'"), 2)))

    enter image description here

    update

    =IFERROR(BYROW(A1:INDEX(A:A, MAX(ROW(A:A)*(A:A<>""))), 
 LAMBDA(x, INDEX(QUERY(SPLIT(FLATTEN(
 FILTER(Values!A:C, Values!D:D<>"")&"​"&
 FILTER(Values!D:D, Values!D:D<>"")), "​"),   
 "select avg(Col2) where Col1 = '"&x&"'"), 2))))