Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
MattBeckwith
 
Posts: n/a
Default How to ignore blank cells


I have a chart which pulls data from various worksheets. The data
changes monthly, so each new month there are lots of blank cells which
don't yet have values. But the chart continues on for the blank days,
giving each a value of zero (even though the cells contains spaces, not
zero). Is there an option to not chart data points when they're blank
(or zero)? Thanks!


--
MattBeckwith


------------------------------------------------------------------------
MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571
View this thread: http://www.excelforum.com/showthread...hreadid=511363

  #2   Report Post  
Posted to microsoft.public.excel.charting
Bernard Liengme
 
Posts: n/a
Default How to ignore blank cells

1) Click chart to activate it
Use Tools | Options; open Chart tab; specify how empty cells are to be
treated
OR
2) Learn about dynamic charts at
http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm
OR
3) use =NA() in blank cells, see
http://www.stfx.ca/people/bliengme/E...issingData.htm
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MattBeckwith"
wrote in message
...

I have a chart which pulls data from various worksheets. The data
changes monthly, so each new month there are lots of blank cells which
don't yet have values. But the chart continues on for the blank days,
giving each a value of zero (even though the cells contains spaces, not
zero). Is there an option to not chart data points when they're blank
(or zero)? Thanks!


--
MattBeckwith


------------------------------------------------------------------------
MattBeckwith's Profile:
http://www.excelforum.com/member.php...o&userid=30571
View this thread: http://www.excelforum.com/showthread...hreadid=511363



  #3   Report Post  
Posted to microsoft.public.excel.charting
MattBeckwith
 
Posts: n/a
Default How to ignore blank cells


Bernard Liengme Wrote:
1) Click chart to activate it
Use Tools | Options; open Chart tab; specify how empty cells are to be
treated


Actually, I had already set it to not plot empty cells, but it does
anyway. It seems I can't attach an Excel file to this message, but if
you give me your email address I'll send it to you. My email address
is . Thanks.


--
MattBeckwith


------------------------------------------------------------------------
MattBeckwith's Profile:
http://www.excelforum.com/member.php...o&userid=30571
View this thread: http://www.excelforum.com/showthread...hreadid=511363

  #4   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default How to ignore blank cells

If the cell contains a formula, even one that returns "", it's not blank. It
contains a formula that returns a text item. Follow Bernard's instructions
for using NA().

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

"MattBeckwith"
wrote in message
news:MattBeckwith.232y4b_1139686501.7405@excelforu m-nospam.com...

Bernard Liengme Wrote:
1) Click chart to activate it
Use Tools | Options; open Chart tab; specify how empty cells are to be
treated


Actually, I had already set it to not plot empty cells, but it does
anyway. It seems I can't attach an Excel file to this message, but if
you give me your email address I'll send it to you. My email address
is . Thanks.


--
MattBeckwith



  #5   Report Post  
Posted to microsoft.public.excel.charting
MattBeckwith
 
Posts: n/a
Default How to ignore blank cells


The referenced web page says:

When an empty cell is filled with =NA(), Excel interpolates the missing
data, joining adjacent data points


I don't want the missing data points interpolated.

However, I went ahead and tried the NA() approach, as follows:

=IF('3'!B230,'3'!B23,NA())


But it just filled the cell with

#NA



--
MattBeckwith


------------------------------------------------------------------------
MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571
View this thread: http://www.excelforum.com/showthread...hreadid=511363



  #6   Report Post  
Posted to microsoft.public.excel.charting
Bernard Liengme
 
Posts: n/a
Default How to ignore blank cells

Yes, that is what we want. Now make the chart.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"MattBeckwith"
wrote in message
...

The referenced web page says:

When an empty cell is filled with =NA(), Excel interpolates the missing
data, joining adjacent data points


I don't want the missing data points interpolated.

However, I went ahead and tried the NA() approach, as follows:

=IF('3'!B230,'3'!B23,NA())


But it just filled the cell with

#NA



--
MattBeckwith


------------------------------------------------------------------------
MattBeckwith's Profile:
http://www.excelforum.com/member.php...o&userid=30571
View this thread: http://www.excelforum.com/showthread...hreadid=511363



  #7   Report Post  
Posted to microsoft.public.excel.charting
MattBeckwith
 
Posts: n/a
Default How to ignore blank cells


(Reply made by mistake.)


--
MattBeckwith


------------------------------------------------------------------------
MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571
View this thread: http://www.excelforum.com/showthread...hreadid=511363

  #8   Report Post  
Posted to microsoft.public.excel.charting
DesM
 
Posts: n/a
Default How to ignore blank cells


I have followed this thread and I have a particular problem that I
thought it was intended to solve.
I am plotting with dates on the X axis. The Y values are function
determined. Using the IF function I have tried giving the cell a NA
value but the plot then extrapolates between the points. I do not want
to give it a zero value.
But I actually want the gap.
I suppose I could, using a macro, copy the series and then delete any
cells with NA in them and then use this as the basis for the plot.
Is there a way of avoiding this?
Des M


--
DesM
------------------------------------------------------------------------
DesM's Profile: http://www.excelforum.com/member.php...o&userid=24121
View this thread: http://www.excelforum.com/showthread...hreadid=511363

  #9   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default How to ignore blank cells

Check Andy Pope's site (http://andypope.info) for a technique to display a
gap for this situation.

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

"DesM" wrote in message
...

I have followed this thread and I have a particular problem that I
thought it was intended to solve.
I am plotting with dates on the X axis. The Y values are function
determined. Using the IF function I have tried giving the cell a NA
value but the plot then extrapolates between the points. I do not want
to give it a zero value.
But I actually want the gap.
I suppose I could, using a macro, copy the series and then delete any
cells with NA in them and then use this as the basis for the plot.
Is there a way of avoiding this?
Des M


--
DesM
------------------------------------------------------------------------
DesM's Profile:
http://www.excelforum.com/member.php...o&userid=24121
View this thread: http://www.excelforum.com/showthread...hreadid=511363



  #10   Report Post  
Posted to microsoft.public.excel.charting
DesM
 
Posts: n/a
Default How to ignore blank cells


John,
Thank you kindly for this. It works fine.
I thouhgt that there might be some function like NA() that made the
cell genuinely blank.
Thanks again.
Des M


--
DesM
------------------------------------------------------------------------
DesM's Profile: http://www.excelforum.com/member.php...o&userid=24121
View this thread: http://www.excelforum.com/showthread...hreadid=511363



  #11   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default How to ignore blank cells

We wish!

- Jon


"DesM" wrote in message
...

John,
Thank you kindly for this. It works fine.
I thouhgt that there might be some function like NA() that made the
cell genuinely blank.
Thanks again.
Des M

--
DesM



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
How to ignore cells in ranking? cardingtr Excel Discussion (Misc queries) 1 September 9th 05 10:29 PM
Formula to delete blank cells across multiple columns? SamFunMail Excel Worksheet Functions 2 September 1st 05 07:05 AM
Copy down - special to fill only the blank cells Mike Excel Discussion (Misc queries) 3 April 18th 05 10:08 PM
how do you ignore blank cells Kerry Excel Discussion (Misc queries) 1 February 16th 05 02:55 PM
Non Blank - Blank Cells???? Reggie Excel Discussion (Misc queries) 3 January 12th 05 01:04 AM


All times are GMT +1. The time now is 06:12 AM.

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

About Us

"It's about Microsoft Excel"