Pivot Table Error

[RussellT]

Thanks in advance.

I create PivotTables where one of the datafields is a Calced Field.
Sometimes the resulting calced field return #DIV/0! because of this the
resulting tables in the PivotTable display #DIV/0! also instead of the
totals. Any way around this problem?

 

[Jim Thomlinson]

Fix your source data to remove the #DIV/0.

=if(A1 = 0, "", B1/A1)

divide by zero yeilds an indeterminate mathematical result. As such XL will
not use it in any calculations as the result of those calcualtions will also
be indeterminate...

 

[RussellT]

The error is a result of the following VBA code not a formula in a cell. any
suggestions?

ActiveSheet.PivotTables("PivotTable2").CalculatedFields.Add "Weighted
Avg Price", _
"=rev / PosSold"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Weighted
Avg Price")
.Orientation = xlDataField
.NumberFormat = "$#,##0"
End With