Weighted Average Ignoring Zeros in Weighting and Value Ranges

[andybason]

Hello

I am trying to calculate a weighted average but some of the values in the range and some of the weightings will often be zero.

This is what I have come up with so far:

=SUMPRODUCT(O231:O240,R231:R240)/COUNTIF(R231:R240,">0")

R contains the weightings and O contains the values but it's not working. The XL2BB is below,

Can anyone see the issue?

Thank you

Book1
	O	P	Q	R
231	0.00			0.00
232	0.00			0.00
233	0.00			0.00
234	23.59	135.60	15.75	0.38
235	35.46	131.27	16.81	0.06
236	0.00			0.00
237	19.24	111.12	15.04	0.10
238	2.43	56.35		0.04
239	0.00			0.00
240	29.13	152.02	20.37	0.42
Summary

Cell Formulas
Range	Formula
O231:O240	O231	=IF(I231="",0,(I231*L231))
P231:Q240	P231	=IF(J231="","",(J231*M231))
R231:R240	R231	=(C231/$C$230)

 

[joeu2004]

At a minimum, replace COUNTIF with SUMIF.

But since 0*x = 0 and 0+x = x (i.e. the zeros make no difference), you could replace SUMIF(R231:R240 ,">0") with simply SUM(R231:R240).

And since your weights should sum to 1 and x/1 = x, you don't need the divisor at all. Simply use SUMPRODUCT as you wrote it.

This is demonstrated below.

Book1
	O			R	S
231	0.00			0.00
232	0.00			0.00
233	0.00			0.00
234	23.59			0.38
235	35.46			0.06
236	0.00			0.00
237	19.24			0.10
238	2.43			0.04
239	0.00			0.00
240	29.13			0.42
241
242				25.3476	R242: =SUMPRODUCT(O231:O240,R231:R240)/SUMIF(R231:R240,">0")
243				25.3476	R243: =SUMPRODUCT(O231:O240,R231:R240)/SUM(R231:R240)
244				25.3476	R244: =SUMPRODUCT(O231:O240,R231:R240)
245				25.3476	R245: =SUM(O234*R234,O235*R235,O237*R237,O238*R238,O240*R240)
Sheet1

(The formula in R245 is the "manual" calculation of the weighted average, just for "proof of concept", not recommended.)

 

[joeu2004]

And since your weights should sum to 1 and x/1 = x, you don't need the divisor at all. Simply use SUMPRODUCT as you wrote it.

PS (too late to edit).... I should have written: __If__ your weights sum to 1, as it appears yours do, you don't need the divisor.

I did __not__ mean to suggest that the weights "should" always sum to 1. And when they don't, we certainly do need to divide by the sum of the weights.

-----

PPS.... If you do not fully understand weighted averages (and the use of COUNTIF instead of SUMIF was not simply a "brain fart"), I suggest that you post all of the other numbers that are involved in the calculations (at least columns C, I and L) and explain their interpretation (e.g. stock price and number of shares) so that we can vet your calculation.

The formulas that I provided do correctly calculate the weighted average of __something__. But it might not be the correct weighted average for your purposes. GIGO!

For example, many people miscalculate average speed by weighting by distance, to wit: SUMPRODUCT(speeds, distances) / SUM(distances). That is indeed the weighted average of __something__ (je ne sais quoi). But in fact, the average speed calculation is weighted by __time__, to wit: SUMPRODUCT(speeds, times) / SUM(times).

 

[andybason]

Thank you Joe, that's fixed it.