Is it possible to use "IF" in a calculation field on a pivot table in Excel?
For example I am calulating the cost per item so, if i had the Calculation CostPerItem = (Cost / Quantity)
I get the result i need if the quantity > 0 i.e. if i spend £1000 on advertising and get 10 enquiries then 1000 / 100 = 10, It's cost me £10 per enquiry
However, if the quantity = 0 then the calc doesn't work as 1000 / 0 = #DIV/0! (in effect nothing if i change the display errors in the cells to 0)
The actual result should be 1000 because i've spent £1000 and got no enquiries so it's cost the £1000.
Can you use the "IF" in the calcutaed field like you could normally to read something like :
=IF(Cost / Quantity > 0, Cost / Quantity, Cost)
Any ideas much appreciated.