Blank cells in a chart
 

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

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


"JackRnl" wrote:

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

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

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


"JackRnl" wrote:


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

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

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

JackRnl wrote:
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

Frank Kabel wrote:

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


"JackRnl" wrote:


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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html