Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. . |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. . |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. . . |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|