Pivot Table with Calculations

[Graemea]

Hi,

I have data arranged as in A1:F11 and would like to be able to summarise the results calculated in C14:F17 by using a Pivot Table rather than formulae.

Is it possible to achieve this with a Pivot Table?

If so could someone kindly please show me how.

Thanks!

Book1
	A	B	C	D	E	F
1	SECURITY	ASSET TYPE	ABS TYPE	WEIGHT	RETURN	CONTRIBUTION
2	AAA	ABS	CLO	1.71	-1.64	-0.03
3	BBB	ABS	CLO	0.89	-0.56	0.00
4	CCC	ABS	CLO	3.75	3.16	0.12
5	DDD	ABS	RMBS	1.53	-1.64	-0.03
6	EEE	ABS	RMBS	2.82	0.65	0.02
7	FFF	ABS	RMBS	3.81	-4.57	-0.17
8	GGG	ABS	RMBS	n/a	n/a	n/a
9	HHH	ABS	ABS OTHER	2.73	-1.86	-0.05
10	III	ABS	ABS OTHER	3.64	-4.24	-0.15
11	JJJ	ABS	ABS OTHER	1.41	-3.04	-0.04
12
13
14			ABS OTHER	34.89	-3.19	-1.11
15			CLO	28.51	1.35	0.38
16			RMBS	36.61	-2.22	-0.81
17				100.00	-1.54	-1.54
Data

Cell Formulas
Range	Formula
F14	=D14/100*E14
F15	=D15/100*E15
F16	=D16/100*E16
F17	=SUM(F14:F16)
D14	=SUMIFS($D$2:$D$11,$C$2:$C$11,$C14)/SUMIFS($D$2:$D$11,$B$2:$B$11,"ABS")*100
D15	=SUMIFS($D$2:$D$11,$C$2:$C$11,$C15)/SUMIFS($D$2:$D$11,$B$2:$B$11,"ABS")*100
D16	=SUMIFS($D$2:$D$11,$C$2:$C$11,$C16)/SUMIFS($D$2:$D$11,$B$2:$B$11,"ABS")*100
D17	=SUM(D14:D16)
E17	=SUMPRODUCT(D14:D16/100,E14:E16)
E14	{=SUMPRODUCT(IFERROR(($C$2:$C$11=$C14)*($D$2:$D$11)*($E$2:$E$11),""))/SUMPRODUCT(IFERROR(($C$2:$C$11=$C14)*($D$2:$D$11),""))}
E15	{=SUMPRODUCT(IFERROR(($C$2:$C$11=$C15)*($D$2:$D$11)*($E$2:$E$11),""))/SUMPRODUCT(IFERROR(($C$2:$C$11=$C15)*($D$2:$D$11),""))}
E16	{=SUMPRODUCT(IFERROR(($C$2:$C$11=$C16)*($D$2:$D$11)*($E$2:$E$11),""))/SUMPRODUCT(IFERROR(($C$2:$C$11=$C16)*($D$2:$D$11),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[steve the fish]

You can do it but its a little fiddly and will need an additional column/s in the source data. The first column is pretty simple. Use SUM of Weight and then Show Values as percentage of column total. The 2nd is going to need a helper column in the source data of Weight*Return. You can then do a calculated field of that column divided by weight. The next column you dont say how that is formulated.

 

[Graemea]

Thank you