Can I formulaically set a cell to blank (non-Text, no value)?

M

michael.tinson

There is an old post from 2003 with the same heading - you may wish to
have a look at the responses there as well. I am trying to make the
value of a cell blank as the result of a formula:

eg: =if(a1>3,BLANK,a2-a3)

If I use "" or nothing at all, I get a text value or 0 which when
graphed will graph as 0. I want these cells to be blank, so that if
they are referenced in a graph (or chart) they don't plot on the graph
at all. At the moment they are plotting as 0.

Any suggestions besides a macro which I think is overkill for my
purposes?

Thanks and Kind regards

Mick
 
R

Ron Rosenfeld

There is an old post from 2003 with the same heading - you may wish to
have a look at the responses there as well. I am trying to make the
value of a cell blank as the result of a formula:

eg: =if(a1>3,BLANK,a2-a3)

If I use "" or nothing at all, I get a text value or 0 which when
graphed will graph as 0. I want these cells to be blank, so that if
they are referenced in a graph (or chart) they don't plot on the graph
at all. At the moment they are plotting as 0.

Any suggestions besides a macro which I think is overkill for my
purposes?

Thanks and Kind regards

Mick
Click to expand...

I believe the graph will ignore NA values, so you could use, in your cell,

=IF(A1>3,NA(),A2-A3)


--ron
 
X

xlbo

That is correct - #N/A will not be charted on a graph

For visual purposes, you can also set the font to white using conditional
formatting and testing for ISNA e.g.

Select chart data
Go Format>Conditional Formatting
change dropdown to "Formula Is"
enter =ISNA(A1)
where the top left cell in your chart data is A1.
Set font to white et voila
--
Rgds, Geoff

"A crash reduces
Your expensive computer
To a simple stone"
 
T

TC

This should leave the cell blank but not sure if graph will work as you want.
=IF(A1>3,"",A2-A3)

TC
 
M

michael.tinson

No, I tried that. It still leaves a value in there that is graphed as
0. The suggestion above with the #N/A values should work from my
testing.

Thanks TC
 
Top