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