22

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)?

23

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.

  • more reference. I can't find any official documentation on when excel started interpreting error as nothing for charts. – Raystafarian Nov 7 '14 at 9:58
  • 2
    Raystafarian - It's nothing new, Excel has done this since at least Excel 97, and probably earlier. Text and most errors are treated as zeros. #N/A is not plotted in a line or XY chart. It does not result in a gap in the line, though (as uncovery says), but a line will connect the points on either side of the #N/A. – Jon Peltier Dec 29 '14 at 3:58
2

Use #N/A to make the chart display blanks in a formula =IF(A1="",#N/A,A1) =IFERROR(A1,#N/A)

1

I found a slight workaround for column charts. If you want to use data points but don't want zeros to show. Change the data point number format to a custom format where zeros equal "".

I used the code:

_(* #,##0_);_(* (#,##0);_(* ""_);_(@_)

All this is is the accounting format with the

_(* #,##0_);_(* (#,##0);_(* **"-"**_);_(@_) 

replaced with

_(* #,##0_);_(* (#,##0);_(* **""**_);_(@_)

Notice "-" replaced with "". the - is zero in accounting format. Hope this helps someone.

Number Formatting

protected by Community Jun 9 '16 at 12:12

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.