… and various sorts of Not a Number.
Back in 2012 (When is an xy chart not an xy chart) I concluded that it was not possible to return an array with empty rows from a user defined function (UDF), and as a result it was not possible to generate a series with gaps in an x-y chart, using a UDF or an array function.
Recently Lori Miller has provided two solutions to this problem (see comments at the link above). First the easy way:
For users of Excel 365 there’s now an option to “show #N/A as an empty cell” under hidden and empty cell settings dialog. Right-click on a chart and select “select data”:
Click “Hidden and Empty Cells” and select “Show #N/A as an empty cell:
The XY chart will now display a break between the two values either side of the #N/A, and will retain all the features of an XY or scatter chart.
For those not using Office 365 it’s not so easy , but the spreadsheet SpecialNum.xlsb (also provided by Lori Miller) has a solution. This spreadsheet has a function returning various types of “not a number”, or NaN. Nan index 3 returns a value that is treated as a blank cell in XY charts (cell C11 below):
This value is the only one that produces the desired result. Index values 0 to 2 and 4 to 5 turn the chart into two vertical lines either side of the NaN:
Index 6 retains the rest of the chart, but has two vertical lines either side of the gap going up (presumably to infinity):
Index 7 is similar, but with the lines going downwards.
Index values 8 to 10 plot as 0:
For those interested in the details, the download file has open-source code for generating the different NaN values, and also a table listing the numbers and their features:
