Blank cells in a chart

[JackRnl]

I have 2 sheets called Sheet-A and Sheet-B both containing numeric data
where (the same) rows and columns are only partly filled. Sheet-B also
contains a chart using data from several columns.

The cells of Sheet-B contain formulas referring to Sheet-A like
=IF(ISBLANK(Sheet-A!D31),
Sheet-A!D31,IF($A7<$C$3,Sheet-A!D31-($C$3-$A7),Sheet-A!D31))
The intention is that if a cell a Sheet-A being empty the corresponding
cell of Sheet-B also will be empty and the chart will NOT use the value
0 for the cell but skip it (and draw a line between the previous and teh
next EXISTING value).
The formula above doesn't achieve what i want (I tried the various
possibilities of Tools | Options | Chart and also Tools | Options | View
| Zero values) and indeed cells that SHOULD be blank aren't (tested with
the =ISBLANK(E8) function)

How can I achieve my goal

 

[Guest]

Hi
try:
=IF(ISBLANK(Sheet-A!D31),
NA(),IF($A7<$C$3,Sheet-A!D31-($C$3-$A7),Sheet-A!D31))

 

[JackRnl]

Thanks for the answer, but unfortunaly it is only a partial solution.
Using the NA() function indeed avoids using 0 in the chart and produces
nice lines, but the "empty" cells now contain "#N/A" making it look awful.
I'm using the "custom format ##0.00;-##0.00" to display values.

Any more help will be appreciated

 

[Debra Dalgleish]

You can use conditional formatting to hide the errors. There are
instructions here:

http://www.contextures.com/xlCondFormat03.html#Errors