Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ian
 
Posts: n/a
Default How do I set a cell to "Empty" so that it does not display in a ch

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(A1100, "", A2)

If I use the #n/a, then the cell isn't plotted in the chart as required.
E.g.
=IF(A1100, #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

  #2   Report Post  
Andy Pope
 
Posts: n/a
Default

Hi Ian,

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

Cheers
Andy

Ian wrote:
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(A1100, "", A2)

If I use the #n/a, then the cell isn't plotted in the chart as required.
E.g.
=IF(A1100, #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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
  #3   Report Post  
Ian
 
Posts: n/a
Default

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

"Andy Pope" wrote:

Hi Ian,

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

Cheers
Andy

Ian wrote:
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(A1100, "", A2)

If I use the #n/a, then the cell isn't plotted in the chart as required.
E.g.
=IF(A1100, #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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

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/
_______

Ian wrote:

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

"Andy Pope" wrote:


Hi Ian,

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

Cheers
Andy

Ian wrote:

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(A1100, "", A2)

If I use the #n/a, then the cell isn't plotted in the chart as required.
E.g.
=IF(A1100, #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


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to display gridlines at irregular intervals? Jason Weiss Charts and Charting in Excel 2 January 3rd 05 08:30 PM
how to cell reference a Chart title Rasoul Khoshravan Azar Charts and Charting in Excel 2 January 1st 05 06:00 PM
make a cell empty based on condition mpierre Charts and Charting in Excel 2 December 29th 04 02:01 PM
Cell value as chart scale maximum Phil Hageman Charts and Charting in Excel 9 December 17th 04 03:09 PM
Axis display as category doco Charts and Charting in Excel 4 December 12th 04 05:45 PM


All times are GMT +1. The time now is 03:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"