4

Why is it that Excel can simply ignore data gaps and connect points with lines?

I have this table that plots as this graph. Whether the gaps are in the table as empty (""), or "#N/A# or "#VALUE!", Excel plots them as zero on the X-axis. I tried telling it to connect data with lines in the menu for hidden and empty cells. That did not make a difference.

How can I fix this graph?

enter image description here

EDITED

Added these comments:

Having IFERROR return #N/A did not make a difference. If I hard code #N/A, write it by hand, I can get this to work. But when it is returned by the IFERROR function with the INDIRECT arguments, Excel still plots the gaps as zero. See the picture.

enter image description here

  • Use general horizontal alignment on the column with numbers and errors. If the errors are not centered in the cells, they are not error values but text, and text is always plotted as zero, even text of "#N/A" or numbers stored as text. – Jon Peltier Feb 11 '18 at 21:22
5

In your example, you're using =IFERROR (good!) to return the value "#VALUE!" (bad!). Your formula is telling Excel to enter a text value of "#VALUE!", which is not 0, #N/A, or "" and so it's plotting a 0 and ignoring your request to Connect Data with Lines.

Update your =IFERROR statement to return NA() as its second argument. Excel will recognize that as an "ignorable" error when charting and leave the points empty.

enter image description here

FWIW, I don't know of anyway to force any error other than #N/A by function-the rest have to occur due to an calculated error condition.

  • Looks like Excel is picky about which #N/A it ignores. I have a call to IFERROR that return #N/A. But Excel still won't plot it right. There must be a format setting that goes along with #N/A for Excel to ignore it. See in the picture above which #N/A works and which won't. I can only tell that one is centered (good plot) and the other is left-justified (bad plot). But I am sure that is something else to it. – user823527 Jan 27 '18 at 2:05
  • 1
    The answer is to call the NA() function instead of typing the "#N/A". It works that way. – user823527 Jan 27 '18 at 2:20
3

Right click the chart and choose Select Data
Modify Hidden and Empty Cells settings

enter image description here

  • Gaps is the default setting
  • Zero will result in data being charted as value, zero value
  • Connect data points with line does exactly as it says, connects data points together

Follow this site for detailed explanation

Edit:

I tried telling it to connect data with lines in the menu for hidden and empty cells. That did not make a difference.

Did you try changing the scale for Y-Axis? Rather than bringing every variable to the scale - would it be possible to cover them in certain ranges? This should fix gaps issue.

To quote from the same site as above
enter image description here

  • This is a good, general comment, but the OP specifically noted that he tried the setting and it did not work for him. – dav Jan 26 '18 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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