If you have a calculated field in your Excel Pivot Table then you may well come across the dreaded #DIV/0! error, which just means that you are attemping to divide a number by zero.
To avoid the error all we need to do is add the IFERROR command in front of our calculated field. You can see in my example below I have a really small extract of data showing some test website ads. I have the cost of the add in Column D and the number of clicks the ads received in Column E.
We can go ahead and create a quick and simple Pivot Table.
- Hit CTRL+A to select our live data cells
- Insert Tab
- Tables Group then choose Pivot Table
- Select to place the Pivot Table on the existing sheet or a new one
We now can easily work out the cost per click for each add by adding a calculated field.
- Click in your Pivot Table to activate the Pivot Table Tools Tab
- Calculations Group
- Fields, Items, and Sets
- Calculated Field
- Call the Filed Cost Per Click
- Type the formula as below
We can see that we have now an error in the result as we have attempted to divide by zero. If we do not fix this we are missing out some great information from this data. Ad i972 has received no clicks!!
We can easily fix this- by adding the IFERROR statement to the formula as below.
Which now gives the result……
…..which is much more insightful.
Why you NEED to know about Pivot Tables
Refresh all of your Pivot Tables with one click
Delete obsolete items from your Pivot Tables blog post or watch the YOUTUBE video..
