Home » Category » Microsoft Excel

Microsoft Excel: Zeros in calculated items in pivot tables

205| Thu, 22 May 2008 04:02:00 GMT| anonymous| Comments (3)
I have a very simple calculated item in a time dimension of a pivot table (Q1
= Jan+Feb+Mar). However because a lot of the data is has null values my
calculated item returns a zero value which increases the number of rows in my
pivot table by 10 times. Anyone know how I can either not show these zeros or
return a null value when the item is calculated? Thanks Tim

Keywords & Tags: zeros, calculated, items, pivot, tables, microsoft, excel

URL: http://www.developertags.com/microsoft-excel/495031/
 
«« Prev - Next »» 3 helpful answers below.
Hi,

Here are a number of possible solutions:
1. With the pt selected choose PivotTable, Table Options, and check the box
for For empty cells, show.
2. Modify your calculated item's formula to read =IF(X+Y=0,"",X+Y) - this
may not meet your needs!
3. Open the filter for the field and uncheck Blanks or whatever it is that
represents your returned results.
4. Double-click the field button for the field in question and uncheck Show
Items with no data, if that is checked.

If none of these work give us more details.
Cheers,
Shane Devenshire

"Tim Wheeler" wrote:

> I have a very simple calculated item in a time dimension of a pivot table (Q1
> = Jan+Feb+Mar). However because a lot of the data is has null values my
> calculated item returns a zero value which increases the number of rows in my
> pivot table by 10 times. Anyone know how I can either not show these zeros or
> return a null value when the item is calculated? Thanks Tim

shanedevenshire | Thu, 22 May 2008 04:03:00 GMT |

Hi Shane

Really appreciate your response.

I am sure your option 2, using an IF statement, is the way to go, but when I
try it my pivot table returns #value! for the calculated items. Your other
options are useful but unfortunatley do not work for my example because,
unless I can get some sort of IF working, my calculated items are returning
zeros rather than nulls.

Thanks for trying, kind regards Tim

"ShaneDevenshire" wrote:

> Hi,
> Here are a number of possible solutions:
> 1. With the pt selected choose PivotTable, Table Options, and check the box
> for For empty cells, show.
> 2. Modify your calculated item's formula to read =IF(X+Y=0,"",X+Y) - this
> may not meet your needs!
> 3. Open the filter for the field and uncheck Blanks or whatever it is that
> represents your returned results.
> 4. Double-click the field button for the field in question and uncheck Show
> Items with no data, if that is checked.
> If none of these work give us more details.
>
> --
> Cheers,
> Shane Devenshire
>
> "Tim Wheeler" wrote:
> > I have a very simple calculated item in a time dimension of a pivot table (Q1
> > = Jan+Feb+Mar). However because a lot of the data is has null values my
> > calculated item returns a zero value which increases the number of rows in my
> > pivot table by 10 times. Anyone know how I can either not show these zeros or
> > return a null value when the item is calculated? Thanks Tim

timwheeler | Thu, 22 May 2008 04:04:00 GMT |

Hi Tim,

How about this then:
Instead of your calculated Item/Field -
1. In the pivot table select the row or column field where your dates are
and choose the command PivotTable, Group and Show Detail, Group. In the
dialog box choose Quarter (and Month) and click OK. Double-click the field
button for the quarter field and change Summarize by to Automatic.
--
Cheers,
Shane Devenshire

"Tim Wheeler" wrote:

> Hi Shane
> Really appreciate your response.
> I am sure your option 2, using an IF statement, is the way to go, but when I
> try it my pivot table returns #value! for the calculated items. Your other
> options are useful but unfortunatley do not work for my example because,
> unless I can get some sort of IF working, my calculated items are returning
> zeros rather than nulls.
> Thanks for trying, kind regards Tim
> "ShaneDevenshire" wrote:
> > Hi,
> >
> > Here are a number of possible solutions:
> > 1. With the pt selected choose PivotTable, Table Options, and check the box
> > for For empty cells, show.
> > 2. Modify your calculated item's formula to read =IF(X+Y=0,"",X+Y) - this
> > may not meet your needs!
> > 3. Open the filter for the field and uncheck Blanks or whatever it is that
> > represents your returned results.
> > 4. Double-click the field button for the field in question and uncheck Show
> > Items with no data, if that is checked.
> >
> > If none of these work give us more details.
> >
> >
> > --
> > Cheers,
> > Shane Devenshire
> >
> >
> > "Tim Wheeler" wrote:
> >
> > > I have a very simple calculated item in a time dimension of a pivot table (Q1
> > > = Jan+Feb+Mar). However because a lot of the data is has null values my
> > > calculated item returns a zero value which increases the number of rows in my
> > > pivot table by 10 times. Anyone know how I can either not show these zeros or
> > > return a null value when the item is calculated? Thanks Tim

shanedevenshire | Thu, 22 May 2008 04:05:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories