PC Review


Reply
Thread Tools Rating: Thread Rating: 7 votes, 5.00 average.

ignoring zero values in excel charts

 
 
Richard
Guest
Posts: n/a
 
      12th Sep 2003
I am trying to create an x/y scatter plot from an excel
sheet where the cells in the data range contain formulae
which sometimes return a zero value - when this occurs I
want the chart to ignore the value as though the cell was
empty but I am having no luck in doing this. It would
also be nice if the LINEST function could be made to
ignore zero values as well.
 
Reply With Quote
 
 
 
 
Joe
Guest
Posts: n/a
 
      12th Sep 2003
Having the same problem. It's okay if there is absolutely
nothing in the cell the chart is pulling from, but if the
actual value is zero, it dips down and looks very
distracting.
I have tried various different ways to get around but none
have been successful as of yet. The way I am currently
trying is I want to put a user-defined formula in the
source data values that will return an array. But any
time I try to enter the function it tells me I am doing it
wrong...not that it doesn't allow you to put function in
charts.
Don't know if this makes sense or not, but if it does in
any way, let me know.
>-----Original Message-----
>I am trying to create an x/y scatter plot from an excel
>sheet where the cells in the data range contain formulae
>which sometimes return a zero value - when this occurs I
>want the chart to ignore the value as though the cell was
>empty but I am having no luck in doing this. It would
>also be nice if the LINEST function could be made to
>ignore zero values as well.
>.
>

 
Reply With Quote
 
 
 
 
Joe
Guest
Posts: n/a
 
      12th Sep 2003
Jerry, I appreciate that! All I needed to know was that
Graphs will ignore #N/A values. Never knew that before.
Thanks for the tip!

Joe B.

>-----Original Message-----
>No function will ignore zero values. If you are trying

to do linear
>regression, you could use the following array formulas

(assuming x-data
>is in A1:A20 and y-data in B1:B20)
>
>=SLOPE(IF(B1:B20=0,"",B1:B20),IF(A1:A20=0,"",A1:A20))
>=INTERCEPT(IF(B1:B20=0,"",B1:B20),IF(A1:A20=0,"",A1:A20))
>
>These must be array entered (Ctrl-Shift-Enter), and the

trick cannot be
>used with LINEST, since LINEST requres a value in every

referenced cell.
>
>Graphs will ignore #N/A values, so you could change each

cell formula to
>something like
>
>=IF(formula=0,#N/A,formula)
>
>for your graph, and modify the preceding SLOPE and

INTERCEPT formulas to
>
>=SLOPE(IF(ISNA(B1:B20),"",B1:B20),IF(ISNA

(A1:A20),"",A1:A20))
>=INTERCEPT(IF(ISNA(B1:B20),"",B1:B20),IF(ISNA

(A1:A20),"",A1:A20))
>
>Jerry
>
>Richard wrote:
>
>> I am trying to create an x/y scatter plot from an excel
>> sheet where the cells in the data range contain

formulae
>> which sometimes return a zero value - when this occurs

I
>> want the chart to ignore the value as though the cell

was
>> empty but I am having no luck in doing this. It would
>> also be nice if the LINEST function could be made to
>> ignore zero values as well.

>
>.
>

 
Reply With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      24th Sep 2003
Tushar Mehta has a routine that blanks out these N/A values, which
aren't plotted at the ends of a series, but are interpolated over
between valid points. I forget the precise name, but it has the words
Chart and N/A in it. Look at the list on the left side of his web site
(http://tushar-mehta.com).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Whirlwind wrote:
> I'm having a similar problem, except that one of my series
> should not be graphed for observations that are the same
> as another series. I put an #N/A in those cells and they
> are not graphed. The problem is if some values show up,
> followed by some #N/As, and then some other values show up
> in a later observation, Excel interpolates between the
> observations that are not #N/A.
>
> Any ideas? Thanks,
>
> JR
>
>>Graphs will ignore #N/A values, so you could change each

>
> cell formula to
>
>>something like
>>
>>=IF(formula=0,#N/A,formula)
>>
>>Jerry
>>
>>Richard wrote:
>>
>>
>>>I am trying to create an x/y scatter plot from an excel
>>>sheet where the cells in the data range contain

>>

> formulae
>
>>>which sometimes return a zero value - when this occurs

>>

> I
>
>>>want the chart to ignore the value as though the cell

>>

> was
>
>>>empty but I am having no luck in doing this. It would
>>>also be nice if the LINEST function could be made to
>>>ignore zero values as well.

>>

>


 
Reply With Quote
 
Member
thepcer's Avatar
Join Date: Jul 2010
Posts: 30
 
      3rd Jun 2011
I found I much easier way!

Plot a new series (hide if you like) with this example command:

if(iserror(value(A1)),na(),A1)

 
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
Ignoring zero data in charts =?Utf-8?B?SmFC?= Microsoft Excel Charting 2 24th Apr 2006 09:22 PM
zero values in charts-don't want to show as zero, but as blank =?Utf-8?B?TUw=?= Microsoft Excel Misc 3 2nd Aug 2005 10:51 PM
Linking Data and Ignoring Zero values =?Utf-8?B?U3RldmUgSG93ZQ==?= Microsoft Excel Worksheet Functions 4 11th Jun 2004 03:19 PM
Resampling an array while ignoring zero values Mozman Microsoft Excel Worksheet Functions 3 13th Nov 2003 03:48 PM
Sorting ignoring alpahnumeric lists ignoring "the" and "an". pbrute Microsoft Excel Discussion 7 28th Oct 2003 12:39 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:19 AM.