Charts with zero values for data points 2

[Watco]

Does anyone know how to not show a data point on a chart (lines) when the data point is zero? The lines connecting data points are going all the way back to zero which is undesireabe. I would rather just not chart the zeroes.
Kevin.

 

[Denial]

The point, and the lines leading into it and out from it, will show on the graph unless either its X or its Y value is an empty cell. This is logical up to a point, since (as the ancient Greeks first pointed out to an astonished world) zero IS a number.

The problems begin when the number to be plotted is actually the result of a formula, and there are some circumstances under which you do not want the point to appear. I have been unable to find a way of achieving this directly: Excel does not provide a way of having a formula's result being seen as "null" by the charting process. A serious weakness, IMHO.

The only workaround I have found is to assign an area of the spreadsheet to be a "copy" of the data you wish to graph. Then write a VBA procedure that firstly nulls that area, then copies into it only the cells that you want to be included in the graph. Drive your graph from this copy area. Set the VBA procedure up so that it will be executed every time any change is made to your worksheet.

Not for the faint of heart!

HTH

 

[aviat]

To start at a number greater than zero on any axis, simple right click on any number in axis, then select: format axis/scale/minium and change to whatever number you want to start with.

 

[IRstuff]

You can do the following:
> wrap an IF statement around the calculations such that if the resulting calculation is zero, to put "" in the cell. Which makes it sort of blank. If you just have data, you can create an adjacent column that has the IF statement checking for zero values and blanking if zero.

> then, turn on the autofilter and select top NN, where NN is the number of data points. Autofilter will not select the blanked cells.

TTFN

 

[joerd]

if you have your formula output #N/A! (by using the function NA() ) Excel won't plot the cell (e.g. when it is zero).
See also thread770-83245

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Denial]

Thanks, Joerd. I was unaware of that. It is an extremely useful piece of information, and it partly solves the problem I discussed above.

The part if does not solve is how to get Excel to leave a gap in the line. Consider a dataset that comprises 5 points A through E. Each point contains a numeric X value and a formula that gives the Y value.
(1) If all points are valid and numeric, then the resulting X-Y graph will go A-B-C-D-E.
(2) If the formula result for point C is NA(), then (as Joerd has told us) the plotted line will go A-B-D-E.
(3) How can one force the line to appear as two separated sub-lines, one going A-B and the other going D-E? In other words, what formula result will force Excel's charting capability treat the cell the same way as it would treat a completely empty cell?

Any good ideas out there on the e-waves?

 

[IRstuff]

Why don't you just plot it with the data markers on? This way, you'll see a missing marker where there was no data?

TTFN

 

[aviat]

If you go down the column with the numbers for the Y axis and delete all the zeros it will chart with gaps between them. It seems that if there is anything in the cell, even a formula to make cell blank, it will go to zero.

 

[joerd]

Denial,

Only workaround for your item (3) I see at the moment is to do it manually: select the series, select the connecting line you want to delete, and format it to show no line. Of course, this is not what you want, you could more easily empty the cells with 0 by hand...:-(

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[JEB66]

to leave a gap in the line when the cells are empty,
click on a chart, the go to "TOOLS" "OPTIONS" and "Chart". then select the what you want.

 

[JohnGP]

JEB66, that only works when cell is empty, no formula, no nothing.

Watco/ Denial, if you don't mind vertical lines happening at either side of the "blanked" portion, you can modify IRstuff's approach and, instead of blanking with "" in the IF statement, insert a large (many orders of magnitude) negative number in the charting column. You need to be charting with straight lines and not "smoothing" to make the points join properly, but you will have near vertical lines at the break.

If you use a large enough number, it will appear as #### in the column anyway.

Advantage over manually deleting zero or blanked cells is that the formula is retained in each of those cells.

 

[macajm]

If you don't want the lines going to/from the zero points (or between non-sequential points where a zero has been eliminated in between by one of the other suggested methods), you can manually eliminate them by clicking on the line segment a second time (the first time selects the whole series, the second time selects only the line segment joining one pair of points), then right-click and choose "Format Data Point", then on the Patterns tab select Line|None. That segment then becomes hidden. Repeat for any other segments you don't want to show.

 

[Aegir]

Use the Autofilter to select rows with your zero results (or error flag, or whatever). Delete the filtered values then turn the autofilter off. Should plot without the joining lines, but will need to be redone manually if your data changes.