If you have got a calculated field on your Excel Pivot Table then you may properly come upon the feared #DIV/0! Error, which simply way that you are attempting to divide quite a number through 0.
There is a definitely simple way to keep away from this error. Let's work thru an example to demonstrate the technique.
To avoid the error all we want to do is insert the IFERROR command in front of our calculated subject. You can see in my instance under I actually have a truly small extract of information displaying a few test website commercials.
I have the value of the advertisement in Column C labelled Cost and the wide variety of clicks the commercials obtained in Column D labelled Clicks.
Cost // Clicks
five.99 // 10
four.88 // 25
4.75 // 7
10.65 // zero
We can go in advance and create a short and easy Pivot Table.
Hit CTRL+A to pick out our stay facts cells
Insert Tab
Tables Group then pick Pivot Table
Select to region the Pivot Table on the present sheet or a new one
We now can without problems workout the value in line with click on for each add through adding a calculated subject.
Click to your Pivot Table to activate the Pivot Table Tools Tab
Select Calculations Group
Got To Fields, Items, and Sets
Click on Calculated Field
Call the Field Cost Per Click
Type the formulation as underneath within the box
=(Cost/Clicks) this can calculate the common fee in step with click on for every advertisement. The effects inside the Pivot Table of the brand new calculated subject can be as below
Cost // Clicks // Cost Per Click
5.Ninety nine // 10 // zero.60
4.88 // 25 // 0.20
four.75 // 7 // zero.Sixty eight
10.Sixty five // 0 // #DIV/0!
We can see that we have now an mistakes in the result as we've tried to divide by zero. If we do no longer restoration this we're lacking out a few extraordinary statistics from this records. The final commercial has received no clicks!!
We can effortlessly fix this- by means of including the IFERROR announcement to the method as below.
=IFERROR(Cost/Clicks,0)
The formulation will search for an errors returned from the Cost/Clicks a part of the method and if an error is returned then Excel will update the error with the price zero.
By doing away with the error the outcomes are much greater insightful as we can no without difficulty see that the last commercial has now not obtained any clicks.
No comments yet