Getting Excel to not plot emty cells

[Paul Finer]

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

 

[Guest]

Paul,

Set the cell to #N/A instead of "" to get the desired result on your chart

 

[Niek Otten]

Using =NA() instead of "" causes the graph to go from the previous point to the next.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Paul Finer]

Paul,

Set the cell to #N/A instead of "" to get the desired result on your chart

Wow, that was quick! Thanks a million...it worked a treat!

Cheers

Paul