"Here's the same question/answer from SO. It seems that you could use a Worksheet_Change macro to accomplish this but the only option is to delete the contents of the cell if the value is empty or zero which, in turn, removes your formula unfortunetely.
"If you use a scatter plot any points you define as =NA() are ommited and no connecting line is drawn. – DaveParillo Jan 9 '10 at 6:14
Only partially correct. NA() or #N/A in a line or XY chart will suppress drawing of a marker. If there are markers on either side of one or more points suppressed by NA(), a line will connect the existing markers across the gap. If one or more suppressed points are at the end of the data range, no line will connect, because there is only a point on one side, and no point to connect to on the other side.
EDIT: A recent (late 2017) enhancement to Excel 2016 treats #N/A like a blank cell. (If you have Office 365, you can toggle this setting.) Finally now we can have a formula produce a result which is plotted as a gap."
