Home 
Search 
Today's Posts 
#1




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




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
How to display gridlines at irregular intervals?  Charts and Charting in Excel  
how to cell reference a Chart title  Charts and Charting in Excel  
make a cell empty based on condition  Charts and Charting in Excel  
Cell value as chart scale maximum  Charts and Charting in Excel  
Axis display as category  Charts and Charting in Excel 