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
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