Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 16th 07, 03:59 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 10
Default Avoid plotting refferanced blanks as zeros in scatter plot graphs

I can not figure out how not to plot data that referances blank cells as
zero's. I have a worksheet that links to tables with data orginized by size
(mm) from big to small. This worksheet contains a scatter plot graph that
looks at all sizes in the table. Typically not all sizes have data. The
only way I can figure out to remove the zero values from the blank cells is
by manualy selecting these cells and clearing the content. I have tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as possible

  #2   Report Post  
Old May 16th 07, 04:12 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default Avoid plotting refferanced blanks as zeros in scatter plot graphs

Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is to
be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank cells as
zero's. I have a worksheet that links to tables with data orginized by
size
(mm) from big to small. This worksheet contains a scatter plot graph that
looks at all sizes in the table. Typically not all sizes have data. The
only way I can figure out to remove the zero values from the blank cells
is
by manualy selecting these cells and clearing the content. I have tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible



  #3   Report Post  
Old May 16th 07, 04:26 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 6,582
Default Avoid plotting refferanced blanks as zeros in scatter plot graphs

If the "blank" is the result of "" in a formula, Tools menu Options
Chart tab 'Plot Empty Cells As' will have no effect.

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


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank cells as
zero's. I have a worksheet that links to tables with data orginized by
size
(mm) from big to small. This worksheet contains a scatter plot graph
that
looks at all sizes in the table. Typically not all sizes have data. The
only way I can figure out to remove the zero values from the blank cells
is
by manualy selecting these cells and clearing the content. I have tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible





  #4   Report Post  
Old May 16th 07, 04:38 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default Avoid plotting refferanced blanks as zeros in scatter plot graphs

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jon Peltier" wrote in message
...
If the "blank" is the result of "" in a formula, Tools menu Options
Chart tab 'Plot Empty Cells As' will have no effect.

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


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank cells as
zero's. I have a worksheet that links to tables with data orginized by
size
(mm) from big to small. This worksheet contains a scatter plot graph
that
looks at all sizes in the table. Typically not all sizes have data.
The
only way I can figure out to remove the zero values from the blank cells
is
by manualy selecting these cells and clearing the content. I have tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible







  #5   Report Post  
Old May 16th 07, 06:12 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 10
Default Avoid plotting refferanced blanks as zeros in scatter plot gra

Thank you guys for your input, NA() does work for not plotting zero values
but now my table is is littered with #N/As. Is it possible to have a to
eliminate this display of the #N/As from the table. Otherwise I will make a
hidden table with NA().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jon Peltier" wrote in message
...
If the "blank" is the result of "" in a formula, Tools menu Options
Chart tab 'Plot Empty Cells As' will have no effect.

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


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank cells as
zero's. I have a worksheet that links to tables with data orginized by
size
(mm) from big to small. This worksheet contains a scatter plot graph
that
looks at all sizes in the table. Typically not all sizes have data.
The
only way I can figure out to remove the zero values from the blank cells
is
by manualy selecting these cells and clearing the content. I have tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible









  #6   Report Post  
Old May 16th 07, 06:52 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Avoid plotting refferanced blanks as zeros in scatter plot gra

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.
--
David Biddulph

"Jobe" wrote in message
news
Thank you guys for your input, NA() does work for not plotting zero
values
but now my table is is littered with #N/As. Is it possible to have a to
eliminate this display of the #N/As from the table. Otherwise I will make
a
hidden table with NA().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jon Peltier" wrote in message
...
If the "blank" is the result of "" in a formula, Tools menu Options
Chart tab 'Plot Empty Cells As' will have no effect.

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


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank cells
as
zero's. I have a worksheet that links to tables with data orginized
by
size
(mm) from big to small. This worksheet contains a scatter plot graph
that
looks at all sizes in the table. Typically not all sizes have data.
The
only way I can figure out to remove the zero values from the blank
cells
is
by manualy selecting these cells and clearing the content. I have
tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible









  #7   Report Post  
Old May 16th 07, 07:15 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 10
Default Avoid plotting refferanced blanks as zeros in scatter plot gra

Thank you

"David Biddulph" wrote:

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.
--
David Biddulph

"Jobe" wrote in message
news
Thank you guys for your input, NA() does work for not plotting zero
values
but now my table is is littered with #N/As. Is it possible to have a to
eliminate this display of the #N/As from the table. Otherwise I will make
a
hidden table with NA().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jon Peltier" wrote in message
...
If the "blank" is the result of "" in a formula, Tools menu Options
Chart tab 'Plot Empty Cells As' will have no effect.

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


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank cells
as
zero's. I have a worksheet that links to tables with data orginized
by
size
(mm) from big to small. This worksheet contains a scatter plot graph
that
looks at all sizes in the table. Typically not all sizes have data.
The
only way I can figure out to remove the zero values from the blank
cells
is
by manualy selecting these cells and clearing the content. I have
tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible










  #8   Report Post  
Old May 16th 07, 07:29 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 10
Default Avoid plotting refferanced blanks as zeros in scatter plot gra

Do I have to do this on a cell by cell basis

"David Biddulph" wrote:

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.
--
David Biddulph

"Jobe" wrote in message
news
Thank you guys for your input, NA() does work for not plotting zero
values
but now my table is is littered with #N/As. Is it possible to have a to
eliminate this display of the #N/As from the table. Otherwise I will make
a
hidden table with NA().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jon Peltier" wrote in message
...
If the "blank" is the result of "" in a formula, Tools menu Options
Chart tab 'Plot Empty Cells As' will have no effect.

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


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank cells
as
zero's. I have a worksheet that links to tables with data orginized
by
size
(mm) from big to small. This worksheet contains a scatter plot graph
that
looks at all sizes in the table. Typically not all sizes have data.
The
only way I can figure out to remove the zero values from the blank
cells
is
by manualy selecting these cells and clearing the content. I have
tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible










  #9   Report Post  
Old May 16th 07, 07:41 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 10
Default Avoid plotting refferanced blanks as zeros in scatter plot gra

Nevermind, I just had to remove the dollar signs. Thanks againJon, Bernard,
and David

"Jobe" wrote:

Do I have to do this on a cell by cell basis

"David Biddulph" wrote:

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.
--
David Biddulph

"Jobe" wrote in message
news
Thank you guys for your input, NA() does work for not plotting zero
values
but now my table is is littered with #N/As. Is it possible to have a to
eliminate this display of the #N/As from the table. Otherwise I will make
a
hidden table with NA().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jon Peltier" wrote in message
...
If the "blank" is the result of "" in a formula, Tools menu Options
Chart tab 'Plot Empty Cells As' will have no effect.

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


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank cells
as
zero's. I have a worksheet that links to tables with data orginized
by
size
(mm) from big to small. This worksheet contains a scatter plot graph
that
looks at all sizes in the table. Typically not all sizes have data.
The
only way I can figure out to remove the zero values from the blank
cells
is
by manualy selecting these cells and clearing the content. I have
tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible










  #10   Report Post  
Old May 16th 07, 07:43 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Avoid plotting refferanced blanks as zeros in scatter plot gra

Either select all the cells (and ensure that A1 is the active cell when you
enter the formula ), or format the one cell and copy format.
--
David Biddulph

"Jobe" wrote in message
...
Do I have to do this on a cell by cell basis

"David Biddulph" wrote:

Format/ Conditional formatting/ Formula is/ =ISNA(A1)
Set font colour to the same as the background colour.
--
David Biddulph

"Jobe" wrote in message
news
Thank you guys for your input, NA() does work for not plotting zero
values
but now my table is is littered with #N/As. Is it possible to have a
to
eliminate this display of the #N/As from the table. Otherwise I will
make
a
hidden table with NA().

"Bernard Liengme" wrote:

Agreed! so use NA() in place of "" in the formula
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jon Peltier" wrote in message
...
If the "blank" is the result of "" in a formula, Tools menu
Options
Chart tab 'Plot Empty Cells As' will have no effect.

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


"Bernard Liengme" wrote in message
...
Replace the blank cell by =NA()
or
With chart select: Tools | Options |Chart and specify how missing
data
is
to be treated
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jobe" wrote in message
...
I can not figure out how not to plot data that referances blank
cells
as
zero's. I have a worksheet that links to tables with data
orginized
by
size
(mm) from big to small. This worksheet contains a scatter plot
graph
that
looks at all sizes in the table. Typically not all sizes have
data.
The
only way I can figure out to remove the zero values from the blank
cells
is
by manualy selecting these cells and clearing the content. I have
tried
several attempts at differant logic ( if(isnumber(A1),A1,""), and
if(isblank(A1),"",A1) ect. and nothing seems to work. I could use
conditional formating but I would like to keep things as simple as
possible














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I avoid plotting 'non-empty' cells germullen Charts and Charting in Excel 2 April 11th 06 02:49 PM
Having trouble plotting blanks on a line graph duncanm Charts and Charting in Excel 1 September 14th 05 03:28 PM
Can I copy x-y scatter plot data direct from one plot to another? Chris Charts and Charting in Excel 2 June 3rd 05 01:20 PM
Plotting Graphs Dharsh Excel Discussion (Misc queries) 2 May 24th 05 01:58 PM
plotting graphs johnd Charts and Charting in Excel 1 March 11th 05 01:52 AM


All times are GMT +1. The time now is 08:46 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017