Tuesday, July 12, 2005

Using #N/A in line chart data

When you have zero data in a line chart it distorts your chart because the zero is plotted on the chart as a valid data point. If however the zero is there because a formula derives it or there is missing data, then this is not the result you want.
There are two solutions.
The first only works if the data is entered as values and can be easily edited. Simply replace zeros with empty cells. This can be done using the CTRL H replace function.
The second way to do this is to use #N/A in the formula.
For example =IF(original_formula=0,#N/A,original_formula).

Apologies for the delay in posting.


Posted by: Jethro | 8:24 AM |