REALLY NEED HELP ON THIS ONE.

[RussellT]

Really Need Help on this one.

I create Pivot Tables using calced fields. Sometimes the calced fields
divide zero by zero which results in error and #DIV/0! showing up in the
RowTotals and
TotalTotals. I can use the Table Options For Error Value Show = (blank),
which
removes the #Div/0! from the table display, the only problem with doing this
is the TotalTotal cells also show up as blank. Here's my code for creating
the calced field.

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

How can I get the PivotTable to display the TotalTotals there is an error in
the rowtotals.

 

[Gary Keramidas]

why not change your formula so that if the divisor is zero, the result in blank or
zero?

 

[RussellT]

Thanks for the input but still have an issue. I change the PivotTable code
to following which works, letting my TotalTotals display the right numbers.

ActiveSheet.PivotTables("PivotTable2").CalculatedFields.Add "Weighted Avg
Price", _ " If(PosSold > 0,rev / PosSold,0"

At issue is this. Now I get a lot of zeros on the table. I've tried
changing the formula so that it reads ,"" but that totally eliminates the
entire calced field from the table. I've tried replacing ,0 with ,
NullString but get the same result a totally eliminated field.

Any suggestions as to what I could put inplace of the ,0 in the code that
would make a zero/zero cell Null or blanks as opposed to zero but still
display the calced field.

 

[Gary Keramidas]

what happens if you choose the option to not display zeroes?
excel 2003
tools/options/view tab
uncheck zero values


click the orb then excel options
click advanced and scroll down to display options for this worksheet
uncheck the box show a zero in cells that have zero value

 

[RussellT]

First let me say thanks for you input. I tried that but then all the zeros
in the table disappear and there are some not resulting from the formulas
that I need to keep displaying.

 

[Gary Keramidas]

maybe you could add the formula to a conditional format so if the field evaluates to
zero, you format the text as white, or whatever color you background is.