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").CalculatedF ields.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.

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

--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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").CalculatedF ields.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.

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").CalculatedF ields.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" wrote:

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

--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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").CalculatedF ields.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.

.

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


--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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").CalculatedF ields.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" wrote:

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

--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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").CalculatedF ields.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.

.

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" wrote:

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


--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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").CalculatedF ields.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" wrote:

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

--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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").CalculatedF ields.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.

.


.

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.

--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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" wrote:

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


--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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").CalculatedF ields.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" wrote:

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

--


Gary Keramidas
Excel 2003


"RussellT" wrote in message
...
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").CalculatedF ields.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.

.


.