Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Please see the below example. I need the weighted average based on the columns.
A | B | C | D | E | F | G | |
Sales Org | WG | Column 1 | Column 2 | Column 3 | Weighted Average | excel formula | |
---|---|---|---|---|---|---|---|
Total | 57,000 | 40 | 42 | sumproduct(F1:F5,C1:C5)/sum(C1:C5) | |||
1 | 10 | co | 40,206 | 25 | 28 | -9.6% | =D1 / E1 -1 |
2 | 10 | ch | 14,200 | 60 | 62 | -2.1% | =D2 / E2 -1 |
3 | 10 | se | 1,361 | 124 | 112 | 10.4% | =D3 / E3 -1 |
4 | 10 | eq | 1,066 | 194 | 178 | 8.5% | =D4 / E4 -1 |
5 | 10 | in | 167 | 289 | 238 | 21.2% | =D5 / E5 -1 |
Need the expression for the weighted average only (Total Row - Yellow text) based on the column .
And i have attached the sample.
i tried the dimensionality(). if (dimensionality() = 0,sum(weighted average * column1)/sum(column1), 'ok').
but not working. is there any chance to sum the column values like sum(total column(n)).
Help Needed.
Regards,
Settu
Try advanced aggregation in your expression for dimensionality() = 0:
=if(dimensionality()=0,
sum(aggr((sum([Column 2]) / sum([Column 3]) -1)*[Column 1], [Sales Org],WG)) / sum([Column 1])
,sum([Column 2]) / sum([Column 3]) -1)