How do I set a cell to "Empty" so that it does not display in a ch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to use the IF statement to set a cell to empty so it doesn't
display in a chart.

If a use the "", then the chart plots this as a zero.
E.g.
=IF(A1>100, "", A2)

If I use the #n/a, then the cell isn't plotted in the chart as required.
E.g.
=IF(A1>100, #n/a, A2)

However, if I then try to use the MAX() or MIN() commands on a cell range
that contains the #n/a, then a #n/a is returned.

So is there either a way of defining a blank (empty) cell or a way of
setting the MAX() and MIN() commands to ignore the #n/a

Thanks in advance
 
Hi Ian,

Rather than using #n/a text use the function NA().
=IF(A1>100, NA(), A2)

Cheers
Andy
 
Thanks Andy,

I have tried the NA() command which plots the graphs ok.
However, assuming I use:
A1 to A10 = [5, 10, 50, 60, 80, 200, 30, 50, 30, 120]
B1 to B10 = [IF(A1>100, NA(), A1), ....]
B12=MAX(B1:B10)

B6 and B10 will be NA(), resulting in B12=MAX(B1:B10) being NA().

Is there any method of getting the MAX() command to ignore the NA() command?
or alternatively using something other than the NA() command to plot the
graphs correctly?

Cheers
 
Ian -

Sometimes you just need to have two sets of data, one for the chart (with NA) and
one for calculations. They're linked of course, so they keep up with the changes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Thanks Andy,

I have tried the NA() command which plots the graphs ok.
However, assuming I use:
A1 to A10 = [5, 10, 50, 60, 80, 200, 30, 50, 30, 120]
B1 to B10 = [IF(A1>100, NA(), A1), ....]
B12=MAX(B1:B10)

B6 and B10 will be NA(), resulting in B12=MAX(B1:B10) being NA().

Is there any method of getting the MAX() command to ignore the NA() command?
or alternatively using something other than the NA() command to plot the
graphs correctly?

Cheers

:

Hi Ian,

Rather than using #n/a text use the function NA().
=IF(A1>100, NA(), A2)

Cheers
Andy
Click to expand...
Click to expand...
 
Back
Top