Home 
Search 
Today's Posts 
#1




How do I ignore Graphs zero values.
I have a variety of tables in excel that sum approx 20 sheets but
occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in. 
#2




How do I ignore Graphs zero values.
select the chart.
goto tools options and check plot empty cells as any of the three chices given there. "fishy" wrote in message ... I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in. 
#3




How do I ignore Graphs zero values.
Hi,
That will not work is the cells actually contain zero because by definition containing zero is NOT empty. The zeros will either need to be removed from the cells or replaced with NA(). =IF(SUM(myrange)=0,NA(),SUM(myrange)) Use conditional formating to hide the #N/A Cheers Andy R..VENKATARAMAN wrote: select the chart. goto tools options and check plot empty cells as any of the three chices given there. "fishy" wrote in message ... I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in.  Andy Pope, Microsoft MVP  Excel http://www.andypope.info 
#4




How do I ignore Graphs zero values.
Working thanks  as you said, already tried the first suggestion but as it
was a formulae, it still showed. "Andy Pope" wrote: Hi, That will not work is the cells actually contain zero because by definition containing zero is NOT empty. The zeros will either need to be removed from the cells or replaced with NA(). =IF(SUM(myrange)=0,NA(),SUM(myrange)) Use conditional formating to hide the #N/A Cheers Andy R..VENKATARAMAN wrote: select the chart. goto tools options and check plot empty cells as any of the three chices given there. "fishy" wrote in message ... I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in.  Andy Pope, Microsoft MVP  Excel http://www.andypope.info 
#5




How do I ignore Graphs zero values.
I have tried doing a conditional format on the #N/A but it does not seem to
work. What do you need to specifically put in the conditional format. I have tried #N/A, NA(), N/A ? "fishy" wrote: Working thanks  as you said, already tried the first suggestion but as it was a formulae, it still showed. "Andy Pope" wrote: Hi, That will not work is the cells actually contain zero because by definition containing zero is NOT empty. The zeros will either need to be removed from the cells or replaced with NA(). =IF(SUM(myrange)=0,NA(),SUM(myrange)) Use conditional formating to hide the #N/A Cheers Andy R..VENKATARAMAN wrote: select the chart. goto tools options and check plot empty cells as any of the three chices given there. "fishy" wrote in message ... I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in.  Andy Pope, Microsoft MVP  Excel http://www.andypope.info 
#6




How do I ignore Graphs zero values.
Use the Is Formula option, and this formula, where the active cell is A1
=ISNA(A1)  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "AR" wrote in message ... I have tried doing a conditional format on the #N/A but it does not seem to work. What do you need to specifically put in the conditional format. I have tried #N/A, NA(), N/A ? "fishy" wrote: Working thanks  as you said, already tried the first suggestion but as it was a formulae, it still showed. "Andy Pope" wrote: Hi, That will not work is the cells actually contain zero because by definition containing zero is NOT empty. The zeros will either need to be removed from the cells or replaced with NA(). =IF(SUM(myrange)=0,NA(),SUM(myrange)) Use conditional formating to hide the #N/A Cheers Andy R..VENKATARAMAN wrote: select the chart. goto tools options and check plot empty cells as any of the three chices given there. "fishy" wrote in message ... I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in.  Andy Pope, Microsoft MVP  Excel http://www.andypope.info 
#7




How do I ignore Graphs zero values.
Jon
I have done this but in an earlier discussion Andy suggested after doing the formula you: " Use conditional formating to hide the #N/A". THis is the part I cannot get to work. Whatever version of N/A I use I cannot fomat it. "Jon Peltier" wrote: Use the Is Formula option, and this formula, where the active cell is A1 =ISNA(A1)  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "AR" wrote in message ... I have tried doing a conditional format on the #N/A but it does not seem to work. What do you need to specifically put in the conditional format. I have tried #N/A, NA(), N/A ? "fishy" wrote: Working thanks  as you said, already tried the first suggestion but as it was a formulae, it still showed. "Andy Pope" wrote: Hi, That will not work is the cells actually contain zero because by definition containing zero is NOT empty. The zeros will either need to be removed from the cells or replaced with NA(). =IF(SUM(myrange)=0,NA(),SUM(myrange)) Use conditional formating to hide the #N/A Cheers Andy R..VENKATARAMAN wrote: select the chart. goto tools options and check plot empty cells as any of the three chices given there. "fishy" wrote in message ... I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in.  Andy Pope, Microsoft MVP  Excel http://www.andypope.info 
#8




How do I ignore Graphs zero values.
I suggest you read Jon's answer again. He's told you exactly how to do it.
 David Biddulph "AR" wrote in message ... Jon I have done this but in an earlier discussion Andy suggested after doing the formula you: " Use conditional formating to hide the #N/A". THis is the part I cannot get to work. Whatever version of N/A I use I cannot fomat it. "Jon Peltier" wrote: Use the Is Formula option, and this formula, where the active cell is A1 =ISNA(A1) "AR" wrote in message ... I have tried doing a conditional format on the #N/A but it does not seem to work. What do you need to specifically put in the conditional format. I have tried #N/A, NA(), N/A ? "fishy" wrote: Working thanks  as you said, already tried the first suggestion but as it was a formulae, it still showed. "Andy Pope" wrote: Hi, That will not work is the cells actually contain zero because by definition containing zero is NOT empty. The zeros will either need to be removed from the cells or replaced with NA(). =IF(SUM(myrange)=0,NA(),SUM(myrange)) Use conditional formating to hide the #N/A .... 
#9




How do I ignore Graphs zero values.
Here's Debra Dalgleish's explanation:
http://contextures.com/xlCondFormat03.html#Errors  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "AR" wrote in message ... Jon I have done this but in an earlier discussion Andy suggested after doing the formula you: " Use conditional formating to hide the #N/A". THis is the part I cannot get to work. Whatever version of N/A I use I cannot fomat it. "Jon Peltier" wrote: Use the Is Formula option, and this formula, where the active cell is A1 =ISNA(A1)  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "AR" wrote in message ... I have tried doing a conditional format on the #N/A but it does not seem to work. What do you need to specifically put in the conditional format. I have tried #N/A, NA(), N/A ? "fishy" wrote: Working thanks  as you said, already tried the first suggestion but as it was a formulae, it still showed. "Andy Pope" wrote: Hi, That will not work is the cells actually contain zero because by definition containing zero is NOT empty. The zeros will either need to be removed from the cells or replaced with NA(). =IF(SUM(myrange)=0,NA(),SUM(myrange)) Use conditional formating to hide the #N/A Cheers Andy R..VENKATARAMAN wrote: select the chart. goto tools options and check plot empty cells as any of the three chices given there. "fishy" wrote in message ... I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in.  Andy Pope, Microsoft MVP  Excel http://www.andypope.info 
#10




How do I ignore Graphs zero values.
Thanks Jon for the How To Conditional Format. I wasn't using Formula Is I was
using Cell Is "Jon Peltier" wrote: Here's Debra Dalgleish's explanation: http://contextures.com/xlCondFormat03.html#Errors  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "AR" wrote in message ... Jon I have done this but in an earlier discussion Andy suggested after doing the formula you: " Use conditional formating to hide the #N/A". THis is the part I cannot get to work. Whatever version of N/A I use I cannot fomat it. "Jon Peltier" wrote: Use the Is Formula option, and this formula, where the active cell is A1 =ISNA(A1)  Jon  Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "AR" wrote in message ... I have tried doing a conditional format on the #N/A but it does not seem to work. What do you need to specifically put in the conditional format. I have tried #N/A, NA(), N/A ? "fishy" wrote: Working thanks  as you said, already tried the first suggestion but as it was a formulae, it still showed. "Andy Pope" wrote: Hi, That will not work is the cells actually contain zero because by definition containing zero is NOT empty. The zeros will either need to be removed from the cells or replaced with NA(). =IF(SUM(myrange)=0,NA(),SUM(myrange)) Use conditional formating to hide the #N/A Cheers Andy R..VENKATARAMAN wrote: select the chart. goto tools options and check plot empty cells as any of the three chices given there. "fishy" wrote in message ... I have a variety of tables in excel that sum approx 20 sheets but occasionally these have a zero value. The graphs link off these tables and show drops to zero on these occurences. How do I get the graph to ignore the zero values. The source table formula is too long to put an if arguement in.  Andy Pope, Microsoft MVP  Excel http://www.andypope.info 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Function: Ignore Mimimum and Maximum Values  Excel Worksheet Functions  
how do i sum up a column of time values but ignore negative values  New Users to Excel  
How can I break values apart that are in the same cell?  Excel Worksheet Functions  
ignore missing values while ploting graph  Excel Discussion (Misc queries)  
Removing 0 or none 0 values from graphs  Charts and Charting in Excel 