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