Manually adding formulas to pivot table

[mattwally]

Hi all,

I'm not sure if what I want to do is even possible but any advice would be really appreciated.

I've created a pivot table as shown in the attached image - sorry that I've had to hide most of the data but I'm not sure how confidential it is so thought I should play it safe.

excelforum.JPG

In between the Job Board Applications column and the Registrations column I want to have a Cost Per Application column.

In this fake example, the company that I've called 'Excel Forum' (imaginative I know) pay $1000 per month for their email marketing, so the cost per application from email would be 1000/1150 = $0.87.

Is there a way to insert a column in a pivot table that you can manually add formulas to?

I've tried replicating the pivot table underneath using cell=B6 etc. and the GETPIVOTDATA function then hiding the whole pivot table apart from the filters, but because there are a different number of subcategories every month the cell references don't work when the filter is changed.

I also tried to add a calculated field but this didn't seem to be what I wanted.

As a last resort I can add the cost per application manually to the raw data and include it as a column when making the pivot table, but this would be an ENORMOUS job to do every month so I'm in need of a better solution!

Apologies for the long post and thank you in advance for any help.

Matt

[noboffinme]

Hi mattwally,

I think a calculated field is the best option.

What problems did you have with it?

Can you post an Excel file with dummy data to work on showing what you've got now & what you want it to look like?

Thanks