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

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(a13,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

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

On 2 May 2006 01:01:46 -0700, "
wrote:

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(a13,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(A13,NA(),A2-A3)


--ron

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

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 FormatConditional 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"


"Ron Rosenfeld" wrote:

On 2 May 2006 01:01:46 -0700, "
wrote:

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(a13,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(A13,NA(),A2-A3)


--ron

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

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

TC

" wrote:

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(a13,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

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

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

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

Thanks Geoff.

That is exactly what I was after.

Regards

MT

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

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?
wrote in message
ups.com...
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