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.