Weighted Average with percentages

[jungle125]

Hi

I am having an issue calculating the weighted average % of this dataset.
I thought that a SUMPRODUCT / sum function will do the trick, but it´s not returning numbers that make any sense, so I´m assuming I´m not using it properly. Basically, if the column on the right has a higher number of data points that created the individual averages in the left column. I expect for examples rows 8 and 15 which contain the highest number of data points and also corresponding high % values to have a bigger influence on the overall % when distributed by weight (formula for weighted average should replace mean average in D3.

Thanks in advance for your help!

01 Master + Observations.xlsx
	D	E
2	Weight average %	Data set size that determined the average
3	36%	39
4	Column4	Column5
5	23%	1
6		0
7	27%	1
8	47%	12
9		0
10	46%	6
11	24%	3
12		0
13		0
14	17%	1
15	59%	14
16		0
17	47%	1
18		0
19		0
MASTER S&P

Cell Formulas
Range	Formula
D3	D3	=AVERAGE(AAPL[Column4])
E3	E3	=SUM(E5:E19)

 

[jdellasala]

This will probably help a LOT!
Video: Weighted average - Microsoft Support
As best as I can tell, the formula you're looking for is:

=SUMPRODUCT(AAPL[Column4],AAPL[Column5])/SUM(AAPL[Column5])

The zeros in Column5 don't change the result ( 0.474924227 or 47.49% ).
In all honesty I had no idea when I first saw this. Always a good day when you learn something!

 

[jungle125]

Hi jdellasala,

you are right! The mistake I made was not separating the two "arrays" column 4 and 5. When I saw the word "array" in the formula, I thought I could automatically select both columns in one go.
Last time I used sumproduct was a very long time ago!

 

[jdellasala]

Hi jdellasala,

you are right! The mistake I made was not separating the two "arrays" column 4 and 5. When I saw the word "array" in the formula, I thought I could automatically select both columns in one go.
Last time I used sumproduct was a very long time ago!

I wouldn't have known what it was without doing a search for it. I'm fine with SUMPRODUCT, but never had to do a weighted average. That short video did the trick though! Glad it helped you too.