Chart line suppression

[Francis Hookham]

Chart problem

XL sheet A1:G3 contain the following:

238 233 222 195 0 0 0
116 123 111 209 0 0 0
354 356 333 404

A3:G3 contain the following formula:

=IF(R[-2]C=0,"",R[-2]C+R[-1]C)

Selecting A3:G3 and inserting a line chart
produces a line whch drops to zero from point 4
and continues to show zero values

Is there a way of surpressing the line from point 4 on
(until values are subsequently added in cols 5 then 6 then 7)?
I hoped "" when R[-2]C=0 is true would do the trick but it does not

Many thanks

Francis Hookham

 

[J.E. McGimpsey]

Chart problem

XL sheet A1:G3 contain the following:

238 233 222 195 0 0 0
116 123 111 209 0 0 0
354 356 333 404

A3:G3 contain the following formula:

=IF(R[-2]C=0,"",R[-2]C+R[-1]C)

Selecting A3:G3 and inserting a line chart
produces a line whch drops to zero from point 4
and continues to show zero values

Is there a way of surpressing the line from point 4 on
(until values are subsequently added in cols 5 then 6 then 7)?
I hoped "" when R[-2]C=0 is true would do the trick but it does not

one way:

A3:G3: =IF(SUM(R[-2]C:R[-1]C)=0,NA(),SUM(R[-2]C:R[-1]C))

will put the #N/A error in your summation cells, which the chart
will ignore. If you don't want to see the #N/As you can use
conditional formatting to hide them:

CF1: Formula is =ISNA(RC)
Format1: Font color same as background color.