Using an ARRAYFORMULA that contains FILTERs in Google Sheets

0
votes

I'm trying to calculate the weighted average of some data for each person B3:B on a list. I pull in the values and weights from a separate sheet based on a filter to match the person in that row of my current sheet, and not include null data.

This is the formula to calculate a single person's weighted average: =AVERAGE.WEIGHTED(FILTER(Form!$G$2:$G, Form!$C$2:$C = $B3, Form!$J$2:$J <> ""), FILTER(Form!$J$2:$J, Form!$C$2:$C = $B3, Form!$J$2:$J <> ""))

But trying to Array it to avoid having the formula in every cell of the column still only gives me the first weighted average. =ARRAYFORMULA(AVERAGE.WEIGHTED(FILTER(Form!$G$2:$G, Form!$C$2:$C = $B3:B, Form!$J$2:$J <> ""), FILTER(Form!$J$2:$J, Form!$C$2:$C = $B3:B, Form!$J$2:$J <> "")))

Is there a way to make this work?

1
google-sheetsgoogle-sheets-formula
AVERAGE.WEIGHTED & FILTER are not supported under ARRAYFORMULA - player0
Can you please provide a sample sheet which contains a sample input and your expected output? Share a test sheet - Ron M

1 Answers

1
votes

=ArrayFormula(VLOOKUP(B2:B,QUERY(QUERY(Form!C:J,"select C,G*J,J where J is not null",1),"select Col1,sum(Col2)/sum(Col3) group by Col1",1),2,FALSE))

  1. Compute the weighted value of each line with the first QUERY
  2. Compute the weighted average of each person with second QUERY
  3. Do ARRAYFORMULA(VLOOKUP on the output