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

[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

 

[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

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

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


--ron

 

[Guest]

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"

 

[Guest]

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

TC

 

[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

 

[michael.tinson]

Thanks Geoff.

That is exactly what I was after.

Regards

MT

 

[PH NEWS]

To continue on from these posts;

If your chart data is like this
A B
s 5
d N/A
f 7
g 10
then as stated in the other posts your graph won't plot the N/A but it still
leaves an empty space marked "d".
Can you get the graph to ignore all N/A/0/blank cells and the headings?