Graphs

[simonw]

I tend to use alot of graphs and use them shorted to show the largest value on the left and therefore the smallest on the right.

Is there any method to automatically alter the range of the graph so zero Y axis values and therefore their corresponding X axis names are not visable on the graph; but will appear as and when data values change.

Regards

Simon

 

[RalphA]

Assuming that your zero values are y values, and that they are, or can, be expressed as a formula, it isn't too difficult to change the formula do that the zero value will not be plotted. say that your formula is, for cell C2, and copied down:
=A2+B2
Then, just change the formula to:
=IF(A2+B2<>0,A1+B2,#N/A)
and copy down.
You will see that the chart will now totally ignore the values x,y for the point where y=0.

 

[simonw]

I tend to use alot of graphs and use them shorted to show the largest value on the left and therefore the smallest on the right.

Is there any method to automatically alter the range of the graph so zero Y axis values and therefore their corresponding X axis names are not visable on the graph; but will appear as and when data values change.

Regards

Simon

 

[tusharm]

Depending on the kind of chart you are creating, it may be possible to create a 'hole' -- using the NA() approach you've already been shown. However, it is far from perfect and it does make it obvious where data have been skipped.

An alternative to consider is to create a compact list of all non-zero values. You will have to *adapt* the technique documented by Chip Pearson at http://www.cpearson.com/excel/noblanks.htm

I tend to use alot of graphs and use them shorted to show the largest value on the left and therefore the smallest on the right.

Is there any method to automatically alter the range of the graph so zero Y axis values and therefore their corresponding X axis names are not visable on the graph; but will appear as and when data values change.

Regards

Simon