How to ignore blank cells

  • Thread starter Thread starter MattBeckwith
  • Start date Start date
M

MattBeckwith

I have a chart which pulls data from various worksheets. The dat
changes monthly, so each new month there are lots of blank cells whic
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, no
zero). Is there an option to not chart data points when they're blan
(or zero)? Thanks
 
Bernard said:
1) Click chart to activate it
Use Tools | Options; open Chart tab; specify how empty cells are to be
treated
Click to expand...

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 (e-mail address removed). Thanks.
 
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" <[email protected]>
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
Click to expand...

I don't want the missing data points interpolated.

However, I went ahead and tried the NA() approach, as follows:
=IF('3'!B23>0,'3'!B23,NA())
Click to expand...

But it just filled the cell with
#NA
Click to expand...
 
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
 
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
 
Back
Top