How can I calculate Weighted Average on google sheets based on a condition?

Question

I wanted to find out a non zero weighted average from 2 arrays in google sheets - the weighted average/sumproduct should be calculated only when value in column A is not zero.

I cannot change or remove data - only a formula can solve this.

+----------+--------+
| Products | Orders |
+----------+--------+
|        5 |   1200 |
|        0 |   3497 |
|       14 |   1234 |
|       13 |    422 |
|        4 |   2444 |
|       21 |  13234 |
+----------+--------+

Desired Result = (5*1200 + 14*1234 + 13*422 + 4*2444 + 21*13234) / (1200 + 1234 + 422 + 2444 + 13234)

^^ Weights are Orders & orders where products = 0 (i.e. 2nd entry) will not be counted in the base

Pls mention the desired result – Rocky May 09 '20 at 07:44 — Rocky, May 09 '20 at 07:44
Hi, I have updated the desired result – mark0018 May 09 '20 at 12:59 — mark0018, May 09 '20 at 12:59

score 5 · Answer 1 · answered May 09 '20 at 10:11

5

if weights are in A column then:

=AVERAGE.WEIGHTED(FILTER(B:B, A:A<>0), FILTER(A:A, A:A<>0))

answered May 09 '20 at 10:11

player0

124,011
12
67
124

score 1 · Accepted Answer · answered May 09 '20 at 17:52

1

Basis your desire result, you can try below formula

=sumproduct((A2:A)*(B2:B))/sumif(A2:A,">0",B2:B)

answered May 09 '20 at 17:52

Rocky

950
1
7
12

How can I calculate Weighted Average on google sheets based on a condition?

2 Answers2