I am trying to visually represent different conditions on a line chart (e.g.
underlying data is rainfall in the UK; plot as a dashed line. The conditions
are times when the wind is from the north, times when the temperature is
below 10C, and both; plot as different thickness lines on top of the
underlying). I've been doing this by calculating when the different
conditions are true in diff't columns, returning the underlying data when
they are and returning N/As when they're not, then cutting and pasting as
values, sorting to delete the N/A's, sorting back by date and displaying as
several lines of different formats on top of each other on the chart. Any
other ways to do this? if I could leave gaps in lines when the formula value
is N/A it would make this much faster, but i can't get it to stop
interpolating unless i clear the cells...

Have your tried enter =NA() in the blank cells? This displays as #N/A which
the chart engine ignores.
Also there is a chart option to specify that blanks are to be ignored and
points are to be joined.
In XL2003; click chart; open Tools | Options | Chart and locate area
specifying how blanks are to be treated
In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of
dialog box "Hidden and Empty Cells"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"mick perry" <mick wrote in message
...
I am trying to visually represent different conditions on a line chart
(e.g.
underlying data is rainfall in the UK; plot as a dashed line. The
conditions
are times when the wind is from the north, times when the temperature is
below 10C, and both; plot as different thickness lines on top of the
underlying). I've been doing this by calculating when the different
conditions are true in diff't columns, returning the underlying data when
they are and returning N/As when they're not, then cutting and pasting as
values, sorting to delete the N/A's, sorting back by date and displaying
as
several lines of different formats on top of each other on the chart. Any
other ways to do this? if I could leave gaps in lines when the formula
value
is N/A it would make this much faster, but i can't get it to stop
interpolating unless i clear the cells...

Bernard -

Don't forget that the "Empty Cells" setting applies only to empty cells,
not cells containing "" or #N/A.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Bernard Liengme wrote:
Have your tried enter =NA() in the blank cells? This displays as #N/A which
the chart engine ignores.
Also there is a chart option to specify that blanks are to be ignored and
points are to be joined.
In XL2003; click chart; open Tools | Options | Chart and locate area
specifying how blanks are to be treated
In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom of
dialog box "Hidden and Empty Cells"
best wishes

Agreed but the OP seems to have really empty cells (missing data)
But I should have added the formula proviso for the sake of others
Cheers
--
Bernard


"Jon Peltier" wrote in message
...
Bernard -

Don't forget that the "Empty Cells" setting applies only to empty cells,
not cells containing "" or #N/A.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Bernard Liengme wrote:
Have your tried enter =NA() in the blank cells? This displays as #N/A
which the chart engine ignores.
Also there is a chart option to specify that blanks are to be ignored and
points are to be joined.
In XL2003; click chart; open Tools | Options | Chart and locate area
specifying how blanks are to be treated
In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom
of dialog box "Hidden and Empty Cells"
best wishes

thanks for the prompt responses - unfortunately I have formulas in the empty
cells so the "Hidden and Empty Cells" instruction still interpolates between
the values (for my chart to work I need to leave gaps in the lines on the
charts, so that you can see which sections of the underlying data fit the
conditions and which ones don't)

"Bernard Liengme" wrote:

Agreed but the OP seems to have really empty cells (missing data)
But I should have added the formula proviso for the sake of others
Cheers
--
Bernard


"Jon Peltier" wrote in message
...
Bernard -

Don't forget that the "Empty Cells" setting applies only to empty cells,
not cells containing "" or #N/A.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Bernard Liengme wrote:
Have your tried enter =NA() in the blank cells? This displays as #N/A
which the chart engine ignores.
Also there is a chart option to specify that blanks are to be ignored and
points are to be joined.
In XL2003; click chart; open Tools | Options | Chart and locate area
specifying how blanks are to be treated
In XL2007: Chart Tools Design; Data group; Select Data tool -- at bottom
of dialog box "Hidden and Empty Cells"
best wishes

Can you change the formula to enter NA() when the plotting data is not
available
Show us the formula so we may help you better
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"mick perry" wrote in message
...
thanks for the prompt responses - unfortunately I have formulas in the
empty
cells so the "Hidden and Empty Cells" instruction still interpolates
between
the values (for my chart to work I need to leave gaps in the lines on the
charts, so that you can see which sections of the underlying data fit the
conditions and which ones don't)

"Bernard Liengme" wrote:

Agreed but the OP seems to have really empty cells (missing data)
But I should have added the formula proviso for the sake of others
Cheers
--
Bernard


"Jon Peltier" wrote in message
...
Bernard -

Don't forget that the "Empty Cells" setting applies only to empty
cells,
not cells containing "" or #N/A.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Bernard Liengme wrote:
Have your tried enter =NA() in the blank cells? This displays as #N/A
which the chart engine ignores.
Also there is a chart option to specify that blanks are to be ignored
and
points are to be joined.
In XL2003; click chart; open Tools | Options | Chart and locate area
specifying how blanks are to be treated
In XL2007: Chart Tools Design; Data group; Select Data tool -- at
bottom
of dialog box "Hidden and Empty Cells"
best wishes