Jump to content


line chart, zeros

line chart zeros

  • Please log in to reply
1 reply to this topic

#1 Jenny M

Jenny M

    Advanced Member

  • Members
  • PipPipPip
  • 32 posts

Posted 08 February 2017 - 04:23 PM

I want to use check boxes to customize a line chart (so users can check which lines they want to compare).  I have a main data table and a range with =IF(data!C6="","n/a",IF($A6=TRUE,data!C6,"n/a")) for the chart to work from.  I don't want zeros in the main data table to show as plunging lines in the chart, but this isn't working.  If the whole row is "n/a" (because the box is unchecked so A6=FALSE) the line doesn't show at all - what I want - but if there's one "n/a" it registers as a zero.  I do have "show empty cells as gaps" checked.


Is there a way to make a chart recognize a cell with a formula as blank?  Am I going about this checkbox thing totally wrong?


Thank you!

#2 Jonathan


    Forums Administrator

  • Administrators
  • 888 posts

Posted 08 February 2017 - 09:17 PM

Hi Jenny,


Excel charts typically treat any cell that contains a formula as having a value, so even a cell that appears blank is treated as having a value of zero if it is generated by a formula.


There is one workaround for this, using the NA() function. The NA function returns the #N/A error code. Cells containing errors are ignored by charts, so you should be able to get the result you need by changing your formula to the following:



You can see more about the #N/A error code (and error codes in general) in the video lesson:

Expert Skills Lesson 4-9 Understand the NUM, DIV0 and NAME error values.


You'll find that Excel doesn't treat cells with errors in exactly the same way as empty cells - a line chart will automatically act as if you chose the connect data points with line option option rather than the gaps option.


You can see more about these options in the video lesson: Essential Skills Lesson 5-17 Deal with empty data points.


I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users