Want chart to be blank (gap) where a formula returns blank

  • Thread starter Thread starter Gav !!
  • Start date Start date
G

Gav !!

Hi guys

How do I get a chart to return a blank where I have written a formula
returning blank based on a value. The problem is I have all my options set
to show only plot visible cells and where a cells is empty leave blank. The
problem is that I have a formula that says if a value is zero make it "" (
blank). My problem is where I have the formula is in my data that I am
adding into my series so when it charts the data it shows every cell that is
visibly blank because of the formula as actually a zero value. If I delete
out the forula my line graph dies exactly where it is supposed to. Hopw do I
get my chart to ignore the fact that there is a formula in there and just
look at the visible aspect of it - which is blank.

Thanks in advance.

Regards

Gav !!
 
This is a FAQ. Formulas cannot return true blanks. #N/A (instead of
"") will not plot as a point (no plot symbol), but will not cause a
break in connecting lines.

If that is not satisfactory, then nothing will behave like an empty cell
except an empty cell. If the data will be updated frequently, then it
might be worth writing a macro tied to the worksheet change event that
will automate deleting or restoring formulas as appropriate.

Jerry
 
Hi Gav,

This may be of help if you don't want to go down the VBA route.
(http://www.andypope.info/charts/brokenlines.htm)
Hi guys

How do I get a chart to return a blank where I have written a formula
returning blank based on a value. The problem is I have all my options set
to show only plot visible cells and where a cells is empty leave blank. The
problem is that I have a formula that says if a value is zero make it "" (
blank). My problem is where I have the formula is in my data that I am
adding into my series so when it charts the data it shows every cell that is
visibly blank because of the formula as actually a zero value. If I delete
out the forula my line graph dies exactly where it is supposed to. Hopw do I
get my chart to ignore the fact that there is a formula in there and just
look at the visible aspect of it - which is blank.

Thanks in advance.

Regards

Gav !!
Click to expand...

--

Cheers
Andy

http://www.andypope.info
 
Slick, but not quite a complete solution. If you have two successive
numeric values between #N/A's, they will not be joined.

Jerry
 
Hi Jerry,

I see want you mean.
It requires another mask series to draw the line back in again!

I will adjust my page to include details of this occurrence.
Slick, but not quite a complete solution. If you have two successive
numeric values between #N/A's, they will not be joined.

Jerry
Click to expand...

--

Cheers
Andy

http://www.andypope.info
 
Back
Top