returning blank cell

[sbigelow]

I have a if formula that looks to see if a referenced cell is blank
(empty cell). If its not, it returns my calculation. If it is blank, I
want it to return a blank cell (empty). Here is the formula:
=IF(ISBLANK(D227),"",(D227-D226)/D226)

The problem is I have a line chart grafted on this data. If the cell
is blank (D227), it is causing my line chart to drop to zero instead of
just ending on the previous value.

I have tried entering an apostrophe but it didn't work. I also tried
turning off zero values in the options menu. Any ideas on how to get
the chart not to plot the cell?

 

[Biff]

Hi!

Try this:

=IF(ISBLANK(D227),NA(),(D227-D226)/D226)

That will "fix" the chart problem but as you'll see, it returns #N/A's in
your worksheet cells.

If don't want to see them use conditional formatting to hide them.

Select the cell
Goto Format>Conditional Formatting
Formula is: =ISNA(cell_reference)
Set the font color to be the same as the background color

Biff

 

[Barb Reinhardt]

I'd probably use

=IF(OR(ISBLANK(D227),ISBLANK(D226)),NA(),(D227-D226)/D226)

Otherwise, you could get a DIV/0 error.