1

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.

player0
  • 124,011
  • 12
  • 67
  • 124
G. Lari
  • 435
  • 2
  • 14

1 Answers1

2

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))))
player0
  • 124,011
  • 12
  • 67
  • 124
  • I tried to modify your formula avoiding the row number like this: `FLATTEN(Values!A1:C&"​"&Values!D1:D)` but it doesn't work. Is there any easy way to make it work? – G. Lari Nov 24 '22 at 12:41
  • @G.Lari try: `=BYROW(A1:A3, LAMBDA(x, INDEX(QUERY(SPLIT(FLATTEN(Values!A1:C&"​"&Values!D1:D), "​"), "select avg(Col2) where Col1 = '"&x&"'"), 2)))` – player0 Nov 24 '22 at 12:50
  • Programming is never boring..... just joking. Yes @player0 your code works. What I tried that didn't work was this `=BYROW(A1:A, LAMBDA(x, INDEX(QUERY(SPLIT(FLATTEN(Values!A1:C&"​"&Values!D1:D), "​"), "select avg(Col2) where Col1 = '"&x&"'"), 2)))`. I simply changed the range at beginning `A1:A`. Doing so googlesheet takes many minutes to calculate the formula. – G. Lari Nov 24 '22 at 13:21
  • @G.Lari answer updated. use 2nd formula – player0 Nov 24 '22 at 13:27
  • 1
    It looks working fantastically well. Many thanks @player0!!!!! P.S. Now I study your answer :) – G. Lari Nov 24 '22 at 13:36
  • @G.Lari try: https://stackoverflow.com/a/74393500/5632629 and: https://stackoverflow.com/a/74281216/5632629 – player0 Nov 24 '22 at 13:41
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249862/discussion-between-g-lari-and-player0). – G. Lari Nov 24 '22 at 13:44