 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / February 2007How 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. select the chart. goto tools options and check plot empty cells as any of the three chices given there. >I have a variety of tables in excel that sum approx 20 sheets but > occasionally these have a zero value. [quoted text clipped - 4 lines] > > The source table formula is too long to put an if arguement in. 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 > select the chart. > goto tools options and check plot empty cells as any of the three chices [quoted text clipped - 8 lines] >> >>The source table formula is too long to put an if arguement in.  SignatureAndy Pope, Microsoft MVP - Excel http://www.andypope.info Working thanks - as you said, already tried the first suggestion but as it was a formulae, it still showed. > Hi, > [quoted text clipped - 23 lines] > >> > >>The source table formula is too long to put an if arguement in. 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 ? > Working thanks - as you said, already tried the first suggestion but as it > was a formulae, it still showed. [quoted text clipped - 26 lines] > > >> > > >>The source table formula is too long to put an if arguement in. 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 _______ >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 [quoted text clipped - 34 lines] >> > >> >> > >>The source table formula is too long to put an if arguement in. 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. > Use the Is Formula option, and this formula, where the active cell is A1 > [quoted text clipped - 45 lines] > >> > >> > >> > >>The source table formula is too long to put an if arguement in. I suggest you read Jon's answer again. He's told you exactly how to do it. SignatureDavid Biddulph > Jon > [quoted text clipped - 3 lines] > part > I cannot get to work. Whatever version of N/A I use I cannot fomat it. >> Use the Is Formula option, and this formula, where the active cell is A1 >> >> =ISNA(A1) >> >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 ? >> >> Working thanks - as you said, already tried the first suggestion but >> >> as >> >> it >> >> was a formulae, it still showed. >> >> > Hi, >> >> > [quoted text clipped - 8 lines] >> >> > >> >> > Use conditional formating to hide the #N/A ... 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 _______ > Jon > [quoted text clipped - 55 lines] >> >> > >> >> >> > >>The source table formula is too long to put an if arguement in. Thanks Jon for the How To Conditional Format. I wasn't using Formula Is I was using Cell Is > Here's Debra Dalgleish's explanation: > [quoted text clipped - 66 lines] > >> >> > >> > >> >> > >>The source table formula is too long to put an if arguement in. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.