"IF" in Excel pivot table calculated field

Posted on 2011-04-28
Last Modified: 2012-05-11
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.
Question by:carlspywell

    Author Comment

    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 :)

    Accepted Solution

    Sorted it, I had the syntax wrong.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
    If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
    Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
    Video by: Zack
    Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now