PC Review


Reply
Thread Tools Rate Thread

Don't plot zero values

 
 
=?Utf-8?B?R2VvcmdlIFdpbHNvbg==?=
Guest
Posts: n/a
 
      21st Oct 2004
I am currently using Excel XP. I have a scatter chart and would only like to
plot non zero values. These are not null values. Is there a chart setting I
can do to skip plotting zero values or do I need to somehow filter/sort the
data first and then plot?
TIA
George
 
Reply With Quote
 
 
 
 
Jon Peltier
Guest
Posts: n/a
 
      22nd Oct 2004
Hey George -

If there are true zeros in the data, perhaps the easiest way to exclude them from
the chart is to use an autofilter on the data that hides the rows with zeros.

Alternatively, you could insert a column to hide the zeros. Say the range with zeros
is in B1:B10. Select C1:C10, and enter this formula into C1:

=IF(B1=0,NA(),B1)

Hold CTRL while pressing Enter, which enters the formula into all selected cells.
This puts ugly #N/A errors into the sheet, but the chart doesn't plot the points.
Hide the errors with conditional formatting, as Debra Dalgleish shows here:

http://contextures.com/xlCondFormat03.html#Errors

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

George Wilson wrote:

> I am currently using Excel XP. I have a scatter chart and would only like to
> plot non zero values. These are not null values. Is there a chart setting I
> can do to skip plotting zero values or do I need to somehow filter/sort the
> data first and then plot?
> TIA
> George


 
Reply With Quote
 
 
 
 
=?Utf-8?B?R2VvcmdlIFdpbHNvbg==?=
Guest
Posts: n/a
 
      25th Oct 2004
Thank you, this is very helpful info.

"Jon Peltier" wrote:

> Hey George -
>
> If there are true zeros in the data, perhaps the easiest way to exclude them from
> the chart is to use an autofilter on the data that hides the rows with zeros.
>
> Alternatively, you could insert a column to hide the zeros. Say the range with zeros
> is in B1:B10. Select C1:C10, and enter this formula into C1:
>
> =IF(B1=0,NA(),B1)
>
> Hold CTRL while pressing Enter, which enters the formula into all selected cells.
> This puts ugly #N/A errors into the sheet, but the chart doesn't plot the points.
> Hide the errors with conditional formatting, as Debra Dalgleish shows here:
>
> http://contextures.com/xlCondFormat03.html#Errors
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> George Wilson wrote:
>
> > I am currently using Excel XP. I have a scatter chart and would only like to
> > plot non zero values. These are not null values. Is there a chart setting I
> > can do to skip plotting zero values or do I need to somehow filter/sort the
> > data first and then plot?
> > TIA
> > George

>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i not plot values as zero? =?Utf-8?B?dXNjYmVu?= Microsoft Excel Charting 3 29th Mar 2006 09:39 PM
how to get rid of "Negative or zero values on log plot" alex22 Microsoft Excel Discussion 2 21st May 2004 03:28 PM
Don't want to plot zero values Kristin Microsoft Excel Charting 2 19th May 2004 06:49 PM
Do not want zero values to plot in a chart =?Utf-8?B?TWljaGVsbGVU?= Microsoft Excel Misc 2 4th Feb 2004 03:41 PM
Cells that "appear" empty plot as zero. Do not want cells to plot. Diane Microsoft Excel Charting 1 7th Aug 2003 06:02 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:12 PM.