Help!

dont show zero vaules in a chart

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Charting RSS
Next:  line-column combination chart, with stacked colum..  
Author Message
Donald Watkins
External


Since: Jun 05, 2006
Posts: 1



PostPosted: Mon Jun 05, 2006 4:09 pm    Post subject: dont show zero vaules in a chart
Archived from groups: microsoft>public>excel>charting (more info?)

I have a chart that is tracking sales for the month, (by day) the chart has
Zero values due to an "IF" statment that has a false or the reference cell is
not yet filled in resulting in a "zero value"
the chart has a trend line that is tending based on all the remaining zero
values .

I have changed the Chart options to not display zeros, but this doesnt solve
it.
Back to top
Andy Pope
External


Since: Feb 27, 2004
Posts: 1840



PostPosted: Tue Jun 06, 2006 12:19 am    Post subject: Re: dont show zero vaules in a chart [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi,

In your formula use the function NA() instead of False.

Cheers
Andy

Donald Watkins wrote:
> I have a chart that is tracking sales for the month, (by day) the chart has
> Zero values due to an "IF" statment that has a false or the reference cell is
> not yet filled in resulting in a "zero value"
> the chart has a trend line that is tending based on all the remaining zero
> values .
>
> I have changed the Chart options to not display zeros, but this doesnt solve
> it.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Back to top
Lori H.
External


Since: Mar 23, 2009
Posts: 3



PostPosted: Mon Mar 23, 2009 2:13 pm    Post subject: Re: dont show zero vaules in a chart [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I'm having a similar problem, using a pie-in-pie chart on Excel 2003 (SP3).
I'm using NA() - this returns the #NA error
BUT when I go to tools > options to set the chart preference to ignore
empty, the option is grayed out (not functional). Is this due to security
settings (I am on a workplace computer), or is there something else I can
check?

"Andy Pope" wrote:

> Hi,
>
> In your formula use the function NA() instead of False.
>
> Cheers
> Andy
>
> Donald Watkins wrote:
> > I have a chart that is tracking sales for the month, (by day) the chart has
> > Zero values due to an "IF" statment that has a false or the reference cell is
> > not yet filled in resulting in a "zero value"
> > the chart has a trend line that is tending based on all the remaining zero
> > values .
> >
> > I have changed the Chart options to not display zeros, but this doesnt solve
> > it.
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>
Back to top
Jon Peltier
External


Since: Mar 19, 2004
Posts: 2010



PostPosted: Mon Mar 23, 2009 6:50 pm    Post subject: Re: dont show zero vaules in a chart [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

The Ignore Empty Cells setting:
- Applies to Line and XY charts for Interpolate/Blank/Zero
- Applies to Column/Bar charts for Blank/Zero
- Applies to Area charts for Interpolate/Zero
- Does not apply at all to Pie charts
- Only applies to empty cells, not to cells containing "" or NA()
NA() results in interpolated lines in Line and XY charts
NA() is treated as zero in other charts
"" and other text and errors are treated as zero in all charts.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Lori H." wrote in message

> I'm having a similar problem, using a pie-in-pie chart on Excel 2003
> (SP3).
> I'm using NA() - this returns the #NA error
> BUT when I go to tools > options to set the chart preference to ignore
> empty, the option is grayed out (not functional). Is this due to security
> settings (I am on a workplace computer), or is there something else I can
> check?
>
> "Andy Pope" wrote:
>
>> Hi,
>>
>> In your formula use the function NA() instead of False.
>>
>> Cheers
>> Andy
>>
>> Donald Watkins wrote:
>> > I have a chart that is tracking sales for the month, (by day) the chart
>> > has
>> > Zero values due to an "IF" statment that has a false or the reference
>> > cell is
>> > not yet filled in resulting in a "zero value"
>> > the chart has a trend line that is tending based on all the remaining
>> > zero
>> > values .
>> >
>> > I have changed the Chart options to not display zeros, but this doesnt
>> > solve
>> > it.
>>
>> --
>>
>> Andy Pope, Microsoft MVP - Excel
>> http://www.andypope.info
>>
Back to top
Lori H.
External


Since: Mar 23, 2009
Posts: 3



PostPosted: Mon Mar 23, 2009 6:50 pm    Post subject: Re: dont show zero vaules in a chart [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

So, is there a way to NOT show 0 values in a pie chart?
I want to data labels (other than 0), but I don't want to delete the labels
for the 0 values because I want the chart to update when the data changes (so
in the future if one of the 0s becomes >0, the data label should return).

"Jon Peltier" wrote:

> The Ignore Empty Cells setting:
> - Applies to Line and XY charts for Interpolate/Blank/Zero
> - Applies to Column/Bar charts for Blank/Zero
> - Applies to Area charts for Interpolate/Zero
> - Does not apply at all to Pie charts
> - Only applies to empty cells, not to cells containing "" or NA()
> NA() results in interpolated lines in Line and XY charts
> NA() is treated as zero in other charts
> "" and other text and errors are treated as zero in all charts.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services, Inc.
> http://PeltierTech.com/WordPress/
> _______
>
>
> "Lori H." wrote in message
>
> > I'm having a similar problem, using a pie-in-pie chart on Excel 2003
> > (SP3).
> > I'm using NA() - this returns the #NA error
> > BUT when I go to tools > options to set the chart preference to ignore
> > empty, the option is grayed out (not functional). Is this due to security
> > settings (I am on a workplace computer), or is there something else I can
> > check?
> >
> > "Andy Pope" wrote:
> >
> >> Hi,
> >>
> >> In your formula use the function NA() instead of False.
> >>
> >> Cheers
> >> Andy
> >>
> >> Donald Watkins wrote:
> >> > I have a chart that is tracking sales for the month, (by day) the chart
> >> > has
> >> > Zero values due to an "IF" statment that has a false or the reference
> >> > cell is
> >> > not yet filled in resulting in a "zero value"
> >> > the chart has a trend line that is tending based on all the remaining
> >> > zero
> >> > values .
> >> >
> >> > I have changed the Chart options to not display zeros, but this doesnt
> >> > solve
> >> > it.
> >>
> >> --
> >>
> >> Andy Pope, Microsoft MVP - Excel
> >> http://www.andypope.info
> >>
>
>
>
Back to top
Jon Peltier
External


Since: Mar 19, 2004
Posts: 2010



PostPosted: Mon Mar 23, 2009 10:25 pm    Post subject: Re: dont show zero vaules in a chart [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

If all you're showing is a value, use a number format that suppresses zeros,
something like

0;;;
0.0%;;;
$#,##0;;;

or whatever. Read about number formats here:

http://peltiertech.com/Excel/NumberFormats.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Lori H." wrote in message

> So, is there a way to NOT show 0 values in a pie chart?
> I want to data labels (other than 0), but I don't want to delete the
> labels
> for the 0 values because I want the chart to update when the data changes
> (so
> in the future if one of the 0s becomes >0, the data label should return).
>
> "Jon Peltier" wrote:
>
>> The Ignore Empty Cells setting:
>> - Applies to Line and XY charts for Interpolate/Blank/Zero
>> - Applies to Column/Bar charts for Blank/Zero
>> - Applies to Area charts for Interpolate/Zero
>> - Does not apply at all to Pie charts
>> - Only applies to empty cells, not to cells containing "" or NA()
>> NA() results in interpolated lines in Line and XY charts
>> NA() is treated as zero in other charts
>> "" and other text and errors are treated as zero in all charts.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services, Inc.
>> http://PeltierTech.com/WordPress/
>> _______
>>
>>
>> "Lori H." wrote in message
>>
>> > I'm having a similar problem, using a pie-in-pie chart on Excel 2003
>> > (SP3).
>> > I'm using NA() - this returns the #NA error
>> > BUT when I go to tools > options to set the chart preference to ignore
>> > empty, the option is grayed out (not functional). Is this due to
>> > security
>> > settings (I am on a workplace computer), or is there something else I
>> > can
>> > check?
>> >
>> > "Andy Pope" wrote:
>> >
>> >> Hi,
>> >>
>> >> In your formula use the function NA() instead of False.
>> >>
>> >> Cheers
>> >> Andy
>> >>
>> >> Donald Watkins wrote:
>> >> > I have a chart that is tracking sales for the month, (by day) the
>> >> > chart
>> >> > has
>> >> > Zero values due to an "IF" statment that has a false or the
>> >> > reference
>> >> > cell is
>> >> > not yet filled in resulting in a "zero value"
>> >> > the chart has a trend line that is tending based on all the
>> >> > remaining
>> >> > zero
>> >> > values .
>> >> >
>> >> > I have changed the Chart options to not display zeros, but this
>> >> > doesnt
>> >> > solve
>> >> > it.
>> >>
>> >> --
>> >>
>> >> Andy Pope, Microsoft MVP - Excel
>> >> http://www.andypope.info
>> >>
>>
>>
>>
Back to top
Jake
External


Since: Apr 04, 2006
Posts: 54



PostPosted: Mon Apr 06, 2009 10:10 am    Post subject: Re: dont show zero vaules in a chart [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hello,

I have a similar problem about displaying zeroes, although mine is not to
display the category label when the value is zero but I don't want to use
filter to hide zeroes in the worksheet.

Please help.

Thanks


Jake

"Jon Peltier" wrote:

> If all you're showing is a value, use a number format that suppresses zeros,
> something like
>
> 0;;;
> 0.0%;;;
> $#,##0;;;
>
> or whatever. Read about number formats here:
>
> http://peltiertech.com/Excel/NumberFormats.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services, Inc.
> http://PeltierTech.com/WordPress/
> _______
>
>
> "Lori H." wrote in message
>
> > So, is there a way to NOT show 0 values in a pie chart?
> > I want to data labels (other than 0), but I don't want to delete the
> > labels
> > for the 0 values because I want the chart to update when the data changes
> > (so
> > in the future if one of the 0s becomes >0, the data label should return).
> >
> > "Jon Peltier" wrote:
> >
> >> The Ignore Empty Cells setting:
> >> - Applies to Line and XY charts for Interpolate/Blank/Zero
> >> - Applies to Column/Bar charts for Blank/Zero
> >> - Applies to Area charts for Interpolate/Zero
> >> - Does not apply at all to Pie charts
> >> - Only applies to empty cells, not to cells containing "" or NA()
> >> NA() results in interpolated lines in Line and XY charts
> >> NA() is treated as zero in other charts
> >> "" and other text and errors are treated as zero in all charts.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Peltier Technical Services, Inc.
> >> http://PeltierTech.com/WordPress/
> >> _______
> >>
> >>
> >> "Lori H." wrote in message
> >>
> >> > I'm having a similar problem, using a pie-in-pie chart on Excel 2003
> >> > (SP3).
> >> > I'm using NA() - this returns the #NA error
> >> > BUT when I go to tools > options to set the chart preference to ignore
> >> > empty, the option is grayed out (not functional). Is this due to
> >> > security
> >> > settings (I am on a workplace computer), or is there something else I
> >> > can
> >> > check?
> >> >
> >> > "Andy Pope" wrote:
> >> >
> >> >> Hi,
> >> >>
> >> >> In your formula use the function NA() instead of False.
> >> >>
> >> >> Cheers
> >> >> Andy
> >> >>
> >> >> Donald Watkins wrote:
> >> >> > I have a chart that is tracking sales for the month, (by day) the
> >> >> > chart
> >> >> > has
> >> >> > Zero values due to an "IF" statment that has a false or the
> >> >> > reference
> >> >> > cell is
> >> >> > not yet filled in resulting in a "zero value"
> >> >> > the chart has a trend line that is tending based on all the
> >> >> > remaining
> >> >> > zero
> >> >> > values .
> >> >> >
> >> >> > I have changed the Chart options to not display zeros, but this
> >> >> > doesnt
> >> >> > solve
> >> >> > it.
> >> >>
> >> >> --
> >> >>
> >> >> Andy Pope, Microsoft MVP - Excel
> >> >> http://www.andypope.info
> >> >>
> >>
> >>
> >>
>
>
>
Back to top
Jon Peltier
External


Since: Mar 19, 2004
Posts: 2010



PostPosted: Wed Apr 08, 2009 3:40 am    Post subject: Re: dont show zero vaules in a chart [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

There are VBA ways:
search Google Groups for my name and 'hide zeros' or 'don't include zeros'
or similar terms

and formulaic ways:
check out Chip Pearson's site, http://cpearson.com, and look for his
techniques for extracting values according to certain rules

Choose your poison.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"Jake" wrote in message

> Hello,
>
> I have a similar problem about displaying zeroes, although mine is not to
> display the category label when the value is zero but I don't want to use
> filter to hide zeroes in the worksheet.
>
> Please help.
>
> Thanks
>
>
> Jake
>
> "Jon Peltier" wrote:
>
>> If all you're showing is a value, use a number format that suppresses
>> zeros,
>> something like
>>
>> 0;;;
>> 0.0%;;;
>> $#,##0;;;
>>
>> or whatever. Read about number formats here:
>>
>> http://peltiertech.com/Excel/NumberFormats.html
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services, Inc.
>> http://PeltierTech.com/WordPress/
>> _______
>>
>>
>> "Lori H." wrote in message
>>
>> > So, is there a way to NOT show 0 values in a pie chart?
>> > I want to data labels (other than 0), but I don't want to delete the
>> > labels
>> > for the 0 values because I want the chart to update when the data
>> > changes
>> > (so
>> > in the future if one of the 0s becomes >0, the data label should
>> > return).
>> >
>> > "Jon Peltier" wrote:
>> >
>> >> The Ignore Empty Cells setting:
>> >> - Applies to Line and XY charts for Interpolate/Blank/Zero
>> >> - Applies to Column/Bar charts for Blank/Zero
>> >> - Applies to Area charts for Interpolate/Zero
>> >> - Does not apply at all to Pie charts
>> >> - Only applies to empty cells, not to cells containing "" or NA()
>> >> NA() results in interpolated lines in Line and XY charts
>> >> NA() is treated as zero in other charts
>> >> "" and other text and errors are treated as zero in all charts.
>> >>
>> >> - Jon
>> >> -------
>> >> Jon Peltier, Microsoft Excel MVP
>> >> Peltier Technical Services, Inc.
>> >> http://PeltierTech.com/WordPress/
>> >> _______
>> >>
>> >>
>> >> "Lori H." wrote in message
>> >>
>> >> > I'm having a similar problem, using a pie-in-pie chart on Excel 2003
>> >> > (SP3).
>> >> > I'm using NA() - this returns the #NA error
>> >> > BUT when I go to tools > options to set the chart preference to
>> >> > ignore
>> >> > empty, the option is grayed out (not functional). Is this due to
>> >> > security
>> >> > settings (I am on a workplace computer), or is there something else
>> >> > I
>> >> > can
>> >> > check?
>> >> >
>> >> > "Andy Pope" wrote:
>> >> >
>> >> >> Hi,
>> >> >>
>> >> >> In your formula use the function NA() instead of False.
>> >> >>
>> >> >> Cheers
>> >> >> Andy
>> >> >>
>> >> >> Donald Watkins wrote:
>> >> >> > I have a chart that is tracking sales for the month, (by day) the
>> >> >> > chart
>> >> >> > has
>> >> >> > Zero values due to an "IF" statment that has a false or the
>> >> >> > reference
>> >> >> > cell is
>> >> >> > not yet filled in resulting in a "zero value"
>> >> >> > the chart has a trend line that is tending based on all the
>> >> >> > remaining
>> >> >> > zero
>> >> >> > values .
>> >> >> >
>> >> >> > I have changed the Chart options to not display zeros, but this
>> >> >> > doesnt
>> >> >> > solve
>> >> >> > it.
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Andy Pope, Microsoft MVP - Excel
>> >> >> http://www.andypope.info
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Charting All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum