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

"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.
  • 2
1 Solution
carlspywellAuthor 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 :)
carlspywellAuthor Commented:
Sorted it, I had the syntax wrong.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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