Divide by Zero Error in Pivot Table

[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 !!!

 

[Guest]

Right click the Table and select Table Options and then define how you want
to display error value...

 

[Guest]

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.