Excel chart, how to make a formula produce gaps

chartsmicrosoft excel

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).

Best Answer

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.