having problems getting line graphs to ignore blank cells

dpolson · Apr 14th, 2002, 07:06 AM

I'm trying to find a way to get Excel line graphs to ignore blank cells.

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

biffmagoo · Apr 17th, 2002, 10:21 AM

I don't have an answer, but I'd like to vehemently agree that this is indeed a frustrating problem.

I tried several of the same methods you did, including N/A, paste-special, etc. I found that similar to your attempts, I could do it only after a couple manipulations, like using Goto to select all cells with "" or #N/A in them that should be blank, and then hitting delete to clear them all at once (saves the time of doing it one at a time). But there really should be a way of working it into the spreadsheet from the start...

Clearly the graph sees the formula in the cell and concludes, "Well, if there's a formula in the cell it's obviously not BLANK"

Hopefully someone else can help...

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 14th, 2002, 07:06 AM	#1
dpolson New Member Join Date: Apr 2002 Posts: 17	I'm trying to find a way to get Excel line graphs to ignore blank cells. 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

Apr 17th, 2002, 10:21 AM	#2
biffmagoo New Member Join Date: Apr 2002 Posts: 1	I don't have an answer, but I'd like to vehemently agree that this is indeed a frustrating problem. I tried several of the same methods you did, including N/A, paste-special, etc. I found that similar to your attempts, I could do it only after a couple manipulations, like using Goto to select all cells with "" or #N/A in them that should be blank, and then hitting delete to clear them all at once (saves the time of doing it one at a time). But there really should be a way of working it into the spreadsheet from the start... Clearly the graph sees the formula in the cell and concludes, "Well, if there's a formula in the cell it's obviously not BLANK" Hopefully someone else can help...