Adding custom columns to pivot table

Question

Hi People,

I am using Excel 2007. The spread sheet I have contains information about shares.  The columns In my spread looks like below.

Script	Activity	Qty	Price Per Share	Trans Amt	Brokerage	Cumulative Transaction Price (Brokerage+Trans Amt)
3IINFO	Buy	100	9.93	993	29.18	963.82
3IINFO	Buy	100	7.7	770	28.93	798.93
ALLBAN	Sell	30	136.7	4101	43.64	4057.36
ALLBAN	Buy	30	134	4020	43.58	4063.58
CENTEX	Sell	15	407	6105	45.92	6059.08

I am trying to create a report that looks like below using pivot tables. But I am unable to create a custom column in the pivot table. Can some one kindly guide me how to achive it. The report I am looking for looks like below.

Script	Total Buy Transactation Amt <br><br>**=**Sum(Trans Price) for Buy	Total Buy Transactation Amt <br><br>**=**Sum(Trans Price) for Sell	Min (Price Per Share)	Max (Price Per Share)	Profit<br><br><br>(Total sell transaction amt - Total Buy transaction Amt)	Profit %
3IINFO	1762.75		7.7	9.93
ALLBAN	4063.58	4057.36	134	136.7
CENTEX	6059.08	6059.08	407	407

(Note: Min and Max price per share is not grouped by the Activity (buy/sell). )

The problem I am facing is, when i group by Activity and sum of transaction amt, the transaction amt gets computed per activity. Kindly help. Please let me know if in case You need any more info.

Thanks in Advance,

S.Sudharsan

Answer 1

Hi,

Had you been using Excel 2010 or a higher version, then I would have suggested using the PowerPivot tool to solve this problem.  SInce you are using Excel 2007, here is my formula based solution

1. In cell B12, enter this formula and copy down

=SUMIFS($G$2:$G$6,$A$2:$A$6,$A12,$B$2:$B$6,B$11)

2. In cell C12, enter this formula and copy down

=SUMIFS($G$2:$G$6,$A$2:$A$6,$A12,$B$2:$B$6,C$11)

3. In cell D12, enter this array formula (Ctrl+Shift+Enter) and copy down

=MIN(IF($A$2:$A$6=A12,$D$2:$D$6))

4. In cell E12, enter this array formula and copy down

=MAX(INDEX(($A$2:$A$6=A12)*($D$2:$D$6),,))

5. In cell F12, enter this formula and copy down

=C12-B12

6. In cell G12, enter this formula and copy down

=IFERROR(F12/B12,"")

Hope this helps.