Getting Excel to not plot emty cells
Paul,
Set the cell to #N/A instead of "" to get the desired result on your chart
"Paul Finer" wrote:
I have a line chart created from a series of data that is calculated
using some formulae.
Sometime the data is not applicable for a particular time period so the
formula looks something like:-
=if(A1=7,"",B1) -- this is not the actual formula but basically if
it's not applicable then I set the cell to double quotes.
My problem is that Excel plots these double quotes as a zero whereas I
would really like to show a gap in the line.
I know there is a preference setting to do this and I have it set correctly.
If I just type in some dummy data and skip a value the chart plots
correctly i.e. produces a gap in the line.
Is there anything I can set in my formula to get Excel to plot a break
in the line?
Many thanks
|