Using an ARRAYFORMULA that contains FILTERs in Google Sheets

Question

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?

AVERAGE.WEIGHTED & FILTER are not supported under ARRAYFORMULA — player0
– player0, Commented Aug 3, 2021 at 19:40
Can you please provide a sample sheet which contains a sample input and your expected output? Share a test sheet — Kristkun
– Kristkun, Commented Aug 3, 2021 at 21:00

idfurw · Accepted Answer · 2021-08-04 05:30:34Z

1

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

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

answered Aug 4, 2021 at 5:30

idfurw

5,8482 gold badges7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using an ARRAYFORMULA that contains FILTERs in Google Sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related