Why Grand Total in Excel Pivot Table # DIV / 0! (Divide by Zero) on this calculated field?
Here are my details:
I created a pivot table from data and created a calculated field called "% Error" defined like this:
I set the Classic Pivot Table option and dragged the values so that they are next to the items instead of at the top, as shown:
The totals at the bottom of month 1, 2, and 3 are correct. The totals for each item are correct. The total below shows "% Error" divided by zero error. I expect it to be (40-30) / 30 = 33.33%. If I ship any quantity of item B in any month it calculates correctly. What do I need to do to get the correct value? Is this a symptom or a bug?
source to share
So, I can tell you what the problem is. I have tried but I cannot find a fix.
You are mostly looking at final, Grand Total
for Total Sum of Percent Error
, since it still has a computed field, however it is not.
For Month
1
, 2
and 3
, you have a computed field formula that you inserted, takes values in Total Sum of Forecast
and Total Sum of Shipped
and applies the computed values in those two cells above it to give you Total Sum of % Error
.
What happens in the column Grand Total
is that it is updated with more than one formula calculated by the field formula Sum
Grand Totals
for both Sum of % Error
for A
and Sum of % Errors
for B
. If you click the button List Formulas
, at least in Excel 2016, you get a new sheet that says this.
Calculated Field
Solve Order Field Formula
1 % Error =(Forecast-Shipped)/Shipped
Calculated Item
Solve Order Item Formula
Note: When a cell is updated by more than one formula,
the value is set by the formula with the last solve order.
To change formula solve orders,
use the Solve Order command on the PivotTable command bar.
The only problem is that you cannot change the Solve Order parameter for this, so the sum of the fields in the column Grand Total
overrides the calculation(40-30)/30
Your option is that under the Design tab you can select Grand Totals
→ For Columns Only
which will give you the totals at the bottom and then manually put the formulas in Grand Total Column
, which is now hidden. Sorry, I cannot think of a more elegant solution.
If you are really ambitious about getting this to work with Pivot Tables
, many years ago I used them to build through VBA
and didn't use inline GUI
, and you get a lot more control over using objects and than you do with the commands that are available to you in the UI. Unfortunately, most of all the knowledge on how to do this has been lost to me.
source to share