Solved

# "IF" in Excel pivot table calculated field

Posted on 2011-04-28
319 Views
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
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 :)
0

Accepted Solution

Sorted it, I had the syntax wrong.
0

## Featured Post

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: …
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.