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

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

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

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

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

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