Divide by Zero Error in Pivot Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know of a work around to avoid Divide by Zero errors in Excel
Pivot Table?

I have a very basic data set:
account Month revenue cost profit
A 1 100 50 50
A 2 100 50 50
A 3 100 50 50
B 1 0 50 -50
B 2 0 50 -50
B 3 0 50 -50

I want to get the composite profit margin for A,B,C using the Pivot Table.
So I create a Field in the Pivot called: Margin = Profit / Revenue.

Month
Data account 1 2 3 Grand Total
Sum of revenue A 100 100 100 300
B 0 0 0 0
Sum of profit A 50 50 50 150
B -50 -50 -50 -150
Sum of Margin A 0.5 0.5 0.5 0.5
B #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Total Sum of revenue 100 100 100 300
Total Sum of profit 0 0 0 0
Total Sum of Margin 0 0 0 #DIV/0!

I expect the the #DIV/0! errors for individual accounts where the Revenue is
0. However, I expect my composite margin to be ZERO because the total profit
is 0 (0/300=0)!

If I do not display the data by month, the Grand Total would not have any
#DIV/0! error.

Data account Total
Sum of revenue A 300
B 0
Sum of profit A 150
B -150
Sum of Margin A 0.5
B #DIV/0!
Total Sum of revenue 300
Total Sum of profit 0
Total Sum of Margin 0

Since the calculated field "margin" automatically caculates the SUM, there
is no way to use the trick of replacing Zero values with null value "". I am
at the mercy of the MS Bug ! The order of calculation is not correct the
Total By Row / By Column !

Help !!!
 
Right click the Table and select Table Options and then define how you want
to display error value...
 
Thanks for the tip. But changing the display options to show blank for error
values does not change the underlying algorithm.

I think MS needs to supply a patch.
 
Back
Top