Treating missing values as gaps when charting arrays

[Admiral Elm]

Hello, longtime lurker, first-time poster here. I didn't happen to see any topics that directly mentioned this, but I apologize if I missed it.

My question is: if I'm populating a chart series with an array formula rather than a range, how do I have missing data points be charted as gaps? It's obvious when I use a range as a series data source, but not with an array.

I know the quick answer is "just use a range instead of an array", but in my current situation, it's preferable to not use ranges.

It's quite easy to replicate this:

1. Add a new 2D line chart
2. Choose "Select Data" for the chart (either via right-click or the ribbon)
3. Click "Add"
4. Under "Series Values", type this and hit OK:
   

   ={1,2,#N/A,4,5}

Here's the problem: Excel charts this as a straight line from (1,1) to (5,5), despite there being no values for 3.

I tried going into the "Select Data" dialog's "Hidden and Empty Cell Settings" and changing the options; I figured "Connect data points with line" was chosen, but it actually wasn't. In fact, Excel seems to ignore this option completely for array formulas.

I tried entering this, but it's invalid:

={1,2,,4,5}

And adding something like this just results in the missing position being converted to 0:

={1,2,"",4,5}

I'm using Excel 2013 on Windows 7, but this problem also occurs in Excel 2010.

Any ideas are appreciated. Thank you.

 

[nuked]

={1,2,0,4,5}

You can then put a custom number format on the label so that 0's don't get shown:

0;-0;""

 

[Admiral Elm]

={1,2,0,4,5}

You can then put a custom number format on the label so that 0's don't get shown:

0;-0;""

Thanks for the quick reply, Nuked, but I suppose I was unclear: I'm actually referring to the data point itself being shown, not the data label.