HOW THE HELL Can I get excell to stop ploting blank cells

[Dan Thompson]

I am pulling my hair out over this stupid thing. I am using MS Excel 2003
I have a column of data that I am trying to graph the problem is the column
derives its data from a formula in the cell if the formula is true than the
cell has a visible data value in it if it is false the cell has no visible
data value just the formula value that shows as a blank cell except for in
the formula window. My problem is that My plot for my chart is from Cells A1
to A50 which all have formulas but only A1 to A20 actualy has visible value
in the spread sheet because the rest of the cells have a formual boolean
value of false and only display a visible value in the cell when the formula
is true. Now when I plot A1 to A50 I still get the line plot on the graph
dropping to a zero value after cell A20 even though the cells in the spread
sheet have no visible values. Oh and I already went into chart settings and
set it to "Do Not Plot Empty Cells" Technicaly the cells I am ploting are not
empty they contain formulas but visibly they are empty untill that formula =
a true value.

Can someone tell me how to plot cells with formulas that don't have a
visible value in that cell on the spread sheet as "NOT Ploted"

Thanks
Dan Thompson

 

[James Silverton]

Dan wrote on Wed, 23 Jul 2008 12:15:01 -0700:

Can someone tell me how to plot cells with formulas that don't
have a visible value in that cell on the spread sheet as "NOT
Ploted"

I'm not absolutely certain what you want but try using Help. If you ask
for something like "plot blank cells", a first option is "Change the way
in which blank cells are plotted".

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[Andy Pope]

Hi,

Excel does not see a cell with formula as being blank.
You can stop the marker from being displayed by using NA() instead of ""
in your formula. You can not however break the line

There is some more info here.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

 

[Jon Peltier]

If the problem is the data stops after 20 rows and the chart is plotting 50,
you need to set up dynamic ranges that only include the first 20 rows with
data, and use these as your chart source. Check these pages:

http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[LarryP]

Seems to me when I ran into that I beat it by changing the formula so where
it now says to return "" or " " or whatever, to return Null. If you try
that and it doesn't work, write a little VBA to do essentially the same
thing: examine all the cells you're concerned with, and anywhere the
formula-generated value is "", replace it with null. (Caution: that will of
course delete your formulas from those cells, and they'll have to be
replaced.)