software

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(a1gt;3,BLANK,a2-a3)

If I use quot;quot; 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

MickOn 2 May 2006 01:01:46 -0700, quot;
gt; wrote:

gt;There is an old post from 2003 with the same heading - you may wish to
gt;have a look at the responses there as well. I am trying to make the
gt;value of a cell blank as the result of a formula:
gt;
gt;eg: =if(a1gt;3,BLANK,a2-a3)
gt;
gt;If I use quot;quot; or nothing at all, I get a text value or 0 which when
gt;graphed will graph as 0. I want these cells to be blank, so that if
gt;they are referenced in a graph (or chart) they don't plot on the graph
gt;at all. At the moment they are plotting as 0.
gt;
gt;Any suggestions besides a macro which I think is overkill for my
gt;purposes?
gt;
gt;Thanks and Kind regards
gt;
gt;Mick

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

=IF(A1gt;3,NA(),A2-A3)--ron

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 Formatgt;Conditional Formatting
change dropdown to quot;Formula Isquot;
enter =ISNA(A1)
where the top left cell in your chart data is A1.
Set font to white et voila
--
Rgds, Geoff

quot;A crash reduces
Your expensive computer
To a simple stonequot;quot;Ron Rosenfeldquot; wrote:

gt; On 2 May 2006 01:01:46 -0700, quot;
gt; gt; wrote:
gt;
gt; gt;There is an old post from 2003 with the same heading - you may wish to
gt; gt;have a look at the responses there as well. I am trying to make the
gt; gt;value of a cell blank as the result of a formula:
gt; gt;
gt; gt;eg: =if(a1gt;3,BLANK,a2-a3)
gt; gt;
gt; gt;If I use quot;quot; or nothing at all, I get a text value or 0 which when
gt; gt;graphed will graph as 0. I want these cells to be blank, so that if
gt; gt;they are referenced in a graph (or chart) they don't plot on the graph
gt; gt;at all. At the moment they are plotting as 0.
gt; gt;
gt; gt;Any suggestions besides a macro which I think is overkill for my
gt; gt;purposes?
gt; gt;
gt; gt;Thanks and Kind regards
gt; gt;
gt; gt;Mick
gt;
gt; I believe the graph will ignore NA values, so you could use, in your cell,
gt;
gt; =IF(A1gt;3,NA(),A2-A3)
gt;
gt;
gt; --ron
gt;

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

TC

quot; wrote:

gt; There is an old post from 2003 with the same heading - you may wish to
gt; have a look at the responses there as well. I am trying to make the
gt; value of a cell blank as the result of a formula:
gt;
gt; eg: =if(a1gt;3,BLANK,a2-a3)
gt;
gt; If I use quot;quot; or nothing at all, I get a text value or 0 which when
gt; graphed will graph as 0. I want these cells to be blank, so that if
gt; they are referenced in a graph (or chart) they don't plot on the graph
gt; at all. At the moment they are plotting as 0.
gt;
gt; Any suggestions besides a macro which I think is overkill for my
gt; purposes?
gt;
gt; Thanks and Kind regards
gt;
gt; Mick
gt;
gt;

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 TCThanks Geoff.

That is exactly what I was after.

Regards

MTTo 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 quot;dquot;.
Can you get the graph to ignore all N/A/0/blank cells and the headings?
gt; wrote in message ups.com...
gt; No, I tried that. It still leaves a value in there that is graphed as
gt; 0. The suggestion above with the #N/A values should work from my
gt; testing.
gt;
gt; Thanks TC
gt;