• Status: Solved
• Priority: Medium
• Security: Public
• Views: 320

# "IF" in Excel pivot table calculated field

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.
0
carlspywell
• 2
1 Solution

Author Commented:
Sorry a bit muddled on the figures there it should read
"if i spend £1000 on advertising and get 10 enquiries then 1000 / 10 = 100, It's cost me £100 per enquiry "

You get the idea though :)
0

Author Commented:
Sorted it, I had the syntax wrong.
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.