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?


--
sbigelow
------------------------------------------------------------------------
sbigelow's Profile: http://www.excelforum.com/member.php...o&userid=27906
View this thread: http://www.excelforum.com/showthread...hreadid=474555

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 FormatConditional Formatting
Formula is: =ISNA(cell_reference)
Set the font color to be the same as the background color

Biff

"sbigelow" wrote in
message ...

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?


--
sbigelow
------------------------------------------------------------------------
sbigelow's Profile:
http://www.excelforum.com/member.php...o&userid=27906
View this thread: http://www.excelforum.com/showthread...hreadid=474555

I'd probably use

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

Otherwise, you could get a DIV/0 error.


"Biff" wrote in message
...
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 FormatConditional Formatting
Formula is: =ISNA(cell_reference)
Set the font color to be the same as the background color

Biff

"sbigelow" wrote
in message ...

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?


--
sbigelow
------------------------------------------------------------------------
sbigelow's Profile:
http://www.excelforum.com/member.php...o&userid=27906
View this thread:
http://www.excelforum.com/showthread...hreadid=474555