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,

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Paul Finer" wrote in message news:2007030908521816807%paulfinerNO@SPAMmaccom...
|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
|
|

On 2007-03-09 09:07:06 +0000, David said:

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

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

Cheers

Paul