Formula to yield a blank that won't be graphed as zero

steve case · May 2, 2015

My data looks like this:

Code:

DATE |  DATA | Formula
1998 |  7112 | =IF(B2=9999,"",B2)
1999 |  9999 | 
2000 |  7059 | 7059

If I copy the formula in C2 on down and graph it out, Excel interprets "" as zero, and not as a blank cell.

Is there a way to have a formula produce a result that will be interpreted as Blank?

Background: Lots of data bases use a string of nines (9999) to represent missing data.

shg · May 2, 2015

Try

=IF(B2=9999, NA(), B2)

steve case · May 2, 2015

shg said:
Try

=IF(B2=9999, NA(), B2)

Thanks for the quick reply.

It works, uh but it fills in what should have been blank with what looks like an interpolation.

shg · May 2, 2015

What do you want it to do?

steve case · May 2, 2015

shg said:
What do you want it to do?

I can use it with the =NA() but it really isn't right.

A straight line graph loos like: ___ using NA()

and Looks like _ _ if I delete the cells with 9999

I'm off to cut the grass for a hour or so (-: Nice day here in SE WI

shg · May 2, 2015

Not sure I follow that. If you want the 9999's plotted as gaps, then

o Select the raw data column and use Find and Replce to delete all instances of 9999

o Select the chart, Design > Select Data, click Hidden and Empty Cells, Show empty cells as: Gaps

Excel doesn't regard the cells that have formulas that return blanks as empty cells.

steve case · May 2, 2015

Back from cutting the grass:

shg said:
Not sure I follow that. If you want the 9999's plotted as gaps,

That's what I want

shg said:
o Select the raw data column and use Find and Replce to delete all instances of 9999

That's what I've been doing

shg said:
o Select the chart, Design > Select Data, click Hidden and Empty Cells, Show empty cells as: Gaps

I haven't needed to do that, but I'll keep it in mind.

shg said:
Excel doesn't regard the cells that have formulas that return blanks as empty cells.

That's the answer I was getting and why I posted (-:

Thanks for the quick replies.

FYI I won't be sending Microsoft any emails telling
'em that Excel should be able to regard a formula
generated "" as a gap

shg · May 2, 2015

So -- sorted?

steve case · May 2, 2015

shg said:
So -- sorted?

?????????????????

shg · May 2, 2015

Sorted, as in, "Do you now have the behavior you want?"

Formula to yield a blank that won't be graphed as zero

steve case

Well-known Member

Excel Facts

shg

MrExcel MVP

steve case

Well-known Member

shg

MrExcel MVP

steve case

Well-known Member

shg

MrExcel MVP

steve case

Well-known Member

shg

MrExcel MVP

steve case

Well-known Member

shg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Formula to yield a blank that won't be graphed as zero

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock