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.