Excel: How to create (truly) blank cells from formula so that they wont show up in a chart

chartsmicrosoft excel

I have a table where track data on a daily base, compare it to a daily target I have set, calculate the gap between the two and display the data on a line chart.
The data has 4 columns:

A. Date (from today until 31-12-2014
C. Actual value (only filled for past dates)
D. Target Value (all filled until 31-12-2014)
E. Gap (C-D)

I wanted the Gap (E) to be empty as long as there is no current date, and thus filled it with the formula:

=IF(ISBLANK(C10), "", C10-D10)

The future dates of Column E correctly display blank.
When I create a chart from the data (with E being on a different axis), the line is not drawn for future dates of column C since the values are blank, but they are drawn for future dates of column E with Zero.

I am assuming that the result of the formula with a "" content of the field is not considered as "blank" so that the chart assumes it to be zero.

How can I make the line of the chart in Column E disappear for dates where there is no value in Column C (and therefore also in Column E)?

Best Answer

Found the answer here. If you make the formula return NA() instead of "", the chart will accept it as empty and not display a line.