Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Charting / February 2004

Tip: Looking for answers? Try searching our database.

Excluding data points in a chart

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JM - 28 Feb 2004 20:56 GMT
Is there some way to specify conditions under which data
points should be excluded in a chart?  In particular, I
have two columns for which I want to create a scatter
plot.  However, some of the rows are missing data for one
or the other column.  Is there a simple way to tell Excel
to ignore such rows?

Jim
Bernard Liengme - 29 Feb 2004 14:05 GMT
Hi Jim,
1) With the chart selected, use Tools|Options and open the Chart tab;
specify what is to happen with missing values
OR
2) Where data is missing enter =NA()
Signature

Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

> Is there some way to specify conditions under which data
> points should be excluded in a chart?  In particular, I
[quoted text clipped - 4 lines]
>
> Jim
JM - 29 Feb 2004 18:55 GMT
Bernard,

Thanks for the tip.  However, the data is the result of
an IF function and needs to be used for other results.

Is there a way to return a blank cell from an IF
function?  Note that returning "" is not the same as
returning a blank cell because the Tools>Options>Chart
suggetion below only works for truly blank cells.

Your second suggestion (returning =NA()) instead of ""
works for the plot but messes up computations based on
the results.  Specifically, I've been unable to get a RANK
() function call to work correctly with #N/A values in
the range provided.

I've tried using array functions to "clean up" the column
with #N/A before applying Rank() but have run into my
usual consternation with array formulas.

So, the best solution is for me to use a blank cell
return if one exists.  Does it?

Thanks,
Jim

>-----Original Message-----
>Hi Jim,
[quoted text clipped - 12 lines]
>
>.
Bernard Liengme - 29 Feb 2004 19:27 GMT
How about using a 'helper' column. Use the formula with NA() in this column
and use this column for the plot. Use the formula with "" for other
calculations. The helper column could be hidden or it could be on another
sheet.
To select two columns that are not neighbours for potting, select first
range of data, hold CTRL, select second range of data, click Chart Wizard

Signature

Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

> Bernard,
>
[quoted text clipped - 44 lines]
> >
> >.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.