getting blank cells to act like empty cells in line graphs

[dpolson]

I've got sheets that automatically generate line graphs, but I've got a problem - when a cell contains an equation that blanks out the cell (e.g., =IF(A1="","",1)), what I'd like is for a point and attached line to that point not to be displayed for the point represented by that cell (like is the case if the cell is empty). Does anyone have any way to make the blank cell with the equation behave like an empty cell when it comes to graphing? I've tried TOOLS>OPTIONS>CHART>Active Chart>"Plot visible cells only", but that doesn't do the trick.

Any help is appreciated.

Dale

 

[Qroozn]

will a1="",0,1 do the trick?

 

[eliW]

On 2002-04-08 23:03, dpolson wrote:
I've got sheets that automatically generate line graphs, but I've got a problem - when a cell contains an equation that blanks out the cell (e.g., =IF(A1="","",1)), what I'd like is for a point and attached line to that point not to be displayed for the point represented by that cell (like is the case if the cell is empty). Does anyone have any way to make the blank cell with the equation behave like an empty cell when it comes to graphing? I've tried TOOLS>OPTIONS>CHART>Active Chart>"Plot visible cells only", but that doesn't do the trick.

Any help is appreciated.

Dale

Try =IF(A1="",NA(),1) instead of
=IF(A1="","",1)

Eli

 

[Dave Hawley]

Hi


I think you will find many useful eaxmples here:
http://www.ozgrid.com/Excel/ChartTips.htm

 

[Andrew70]

=IF(ISNUMBER(A1);A1;NA())

A1 can be a cell or formula.

 

[SamS]

Dave, I use the NA() method frequently for line graphs but have been unable to Condition Format the cells so that they don't show #NA in the cell, do you know if and how it can be done.

I have found one method which works though is not always practicable whereby I use a Combo Box to display a specific named range combined with an array formula which will display #NA as a blank cell. This is OK for some of the work I do but I would still like to do a Conditional Format for NA().

Many Thanks if you can solve this for me.

 

[dpolson]

Thanks all for the help. Using the NA() solved half of the problem - it got rid of the data point on the graph, but the graph line remains and runs through the spot of the missing datapoint. I'd also like to have it not display the line to/from the absent datapoint. Does anyone know a way to do this?

Dale

 

[dpolson]

Here's what I've learned so far...

These don't do the job at all:
[1] =if(a1="","",1)
[2] formula as in [1], then copy-paste special-values to another cell

These do half of the job:
[3] =if(a1="",NA(),1)
[4] formula as in [3], then copy-paste special-values to another cell

It seems that for the graph to not only not display the datapoint, but also not display the connecting line (which is the goal):

[5] the data points have to be hand-entered into empty cells the hard way
[6] or you do [1][2] or [3][4], then delete the blank cells to make them empty

Three questions:

Q1 - What does Excel leave behind in the blank cell after doing a copy-paste special-values of an equation that generates a "" in the original cell so that it's not really empty and thus doesn't graph like an empty cell?

Q2 - Is there a way using functions & equations around this?

Q3 - If not, is there a way using macros/VBA around this?

I'd appreaciate anyones wisdom.

Dale