Excel chart, how to make a formula produce gaps?

Question

I have a chart in Excel 2007 that is basically just a line graph.

Some cells that make up the series data cannot produce a value through their formula, and I'd like the chart to place a gap in that location, but no matter what I do, the fact that there is a formula in there seems to make the series consider this as a zero, or similar.

If I hit Delete on that cell, the gap appears, but when I add a formula, a line that connects the points on either side appears.

Is there any way I can return something with an IF function that produces the gap?

IF(somecondition; SUM(...); "")
                            ^^-  what to add here, is it possible?

The following does not work:

IF (somecondition; SUM(...); "")
IF (somecondition; SUM(...);)
IF (somecondition; SUM(...))
IF (somecondition; SUM(...); NA)
IF (somecondition; SUM(...); 1/0)

They are all still drawn as though the cell contained 0 (zero).

Answer 1

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.

https://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel

Answer 2

I'd normally say return NA(). However, in a line graph, even if you set the series preferences to leave gaps ("Select data" -> "Hidden and empty cells" -> "Show empty cells as:" -> "Gaps"), the line will just connect continuously to the next available point in the series. If you do not mind changing to another chart type, e.g. the area chart will leave the gaps.

Answer 3

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.

Answer 4

Well, since it's a line graph, you could try setting the cell value to be the average between the previous and the next dot in the graph. For instance:

IF (somecondition; SUM(...); (nextcell + prevcell)/2 )

As I see it, if you did manage to place a gap in this cell, than excel would simply link the previous and next cells with a straight line segment (it is a line graph after all). However, by setting the middle cell's value to the average between its neighboors (instead of blank), you are ensuring that excell does indeed link the neighboor cells with a straight line segment, just as if the middle cell were blank.

This method has two disadvantages:

1. If you are performing some kind of statistical analysis, such as an average or an MSD, this middle cell will affect the result eventhough it should not.
2. I'm not sure, but it will probably give buggy results if two neighbors are supposed to be blank (since one would try to calculate its value based on the other).