Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
That is considered to be zero, which makes the graph hit bottom for that point. I want the graph to disappear/stop. –  Lasse V. Karlsen Jan 8 '10 at 14:11
add comment

4 Answers

up vote 1 down vote accepted

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.

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

share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

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).
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.