weighted average formula

[settu_periasamy]

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

[swuehl]

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)