Would you like to send me a sample file
Please get my email address from my website

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"mick perry" wrote in message
...
thanks - but unfortunately using "" instead of NA() as in

=IF(OR(AND(C162,C16<6),AND(C166,C16<10)),C16,"")

causes zero values to be displayed on the chart instead of gaps (even when
the 'Show Empty Cells' button in 'Hidden & Empty Cells' has been set to
'Gaps')



"Bernard Liengme" wrote:

The NA() part of the formulas cause the chart engine to ignore the cell
and
joint adjacent points.
If you rewrite the formulas in the form =IF(AND(A12,A1<6),A1,"") you
will
get gaps when the cell displays a blank
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"mick perry" wrote in message
...
As an example, if in column A rows 1-10 i have the values 1, 2...10
etc.
In
cell B1 I have a formula =IF(AND(A12,A1<6),A1,NA()) which is then
duplicated
down to B10. In C1 I have the formula =IF(AND(A16,A1<10),A1,NA()).

I want to plot column A as a line chart (dashed for example), and then
I
want to plot columns B and C as line charts on top of it in different
colours, line styles etc to highlight different conditions on the
curve.
This works fine.

The problem is that if the formula in columns B and C are more
complicated
and select non-contiguous sections of column A then the lines are
interpolated. So for instance if the formula in col B is
=IF(OR(AND(C172,C17<6),AND(C176,C17<10)),C17,NA( )) then excel draws
one
line from 3 to 9, rather than two lines going from 3-5 and 7-9. The
only
way
i can stop it interpolating between the two sections is to delete the
folrmula, but this isn't practical in spreadsheets with thousands of
rows
and
many columns of conditions. So - I'm looking for a way to leave
formulas
in
all the cells but not have excel join up the lines where there isn't
any
data. Please let me know if this doesn't make sense. Thanks...

"Bernard Liengme" wrote:

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