If i have a formula on a pivot table that calculates some results no problem.

I want to add an IF to the formula so that if a field is a value it uses the formula but if it's zero i need it to drop in a value from a cell outside the pivot table.

For example if i had the formula to work out the cost we are paying for advertising, the formula would be

= Cost * Number of Enquiries

If i have a seperately worked out (outside the pivot) cost for the Internet in Cell M4 can i use this in the formula? i.e.

=IF (Source <> "Internet", Cost * Number of Enquiries, Value in Cell M4)

Following should work:
=IF(D2<>"Internet", GETPIVOTDATA("Marks",$B$11,"Name","A"), $M$4)

See an example is attached. See the formulas in Column E with heading "Formula column". Example.xls

0

carlspywellAuthor Commented:

I need it the other way round. I need to pull the information from M4 into the result on the pivot table if source is internet.

If you look at the attached sample, I have the cost for all other Sources from the data. Internet is a bit more complicated and is based on a monthly fee plus 1-5 enquires at so much, 6-10 at another rate etc. (i've not included this formula just the total in M4)

The Total Calculation is - =IF(internet= 0,Cost, "Value in M4")

I need it to put the Value in M4 in if "Internet?" equals 1

Hmm.. the values inside the pivot table are computed by Excel internally using GETPIVOTDATA(). I donno if you can change that.
It would be much easier of course to change your source data.
Sorry but I didn't quite understand your usecase.

What I've done now is added a new column to your data set called "New Cost". This has the value from M4 if the source is Internet, else it uses the normal cost.
Now use this column in the pivot table.

Sorry but how is what you've finally chosen different from what I suggested in the post 35697216?
"What I've done now is added a new column to your data set called "New Cost". This has the value from M4 if the source is Internet, else it uses the normal cost."

0

carlspywellAuthor Commented:

Point Taken @theKashyap. However, your solution does not work in my situation but as it would work if the data source was within the spreedsheet, i'll award the points.

My solution is different because:

If you read my response, your solution added a column to the data source! I cannot add a column to the data source as the data source is external and cannot be manipulated from the spreadsheet.

In the attached sample, I used the data from the cells "Source", "Enquiries" & "Cost" to create a new column "Solution" outside the table, and used the formula "=IF(A12="Internet",M4,C12*B12)" to give the result.
If Souce = Internet it uses M4 otherwise it Calculates the result by multiplying the Enquiries by the Cost.