Ignoring Errors in Chart

Hello,

How do I ignore errors in a line chart?
Let's say range a1:a10 are values and a5:a10 all contain #DIV/0!
Currently my chart plots those as zeros. How do I exclude those points?

Thanks!

Originally Posted by Hoffhines

Hello,

How do I ignore errors in a line chart?
Let's say range a1:a10 are values and a5:a10 all contain #DIV/0!
Currently my chart plots those as zeros. How do I exclude those points?

Thanks!

Perhaps stating the obvious but the simplest way would be to adjust the range plotted to be A1:A4.

If the div/0 are in a non contiguous range then the other option is to use a datafilter to extract all the valid plots to a new range of cells, and then have the chart based on this new range. If you use range names in the chart to identify the sources, and assuming you use a macro to perform the data filter, have it finish off by redefining the range name to cover your filtered range. Better still make the filtered range the object of a dynamic range name.

HTH

I tried the data filter to take out all zero values (that's what i'm trying to eliminate). When i had it copied to another cell and then refrenced the chart to the new cells it continued to plot the 0 value. Am I missing something? Under tools/options/chart I have "not plotted" for empty cells, but I don't think it's reading it as empty. I just don't want zero's in the line graph. I want it to have a gap between plotts if there is a zero value, sounds simple enough! Ha!

Thanks for your help

If the cell in the data range contains anything then excel will try and plot it.
Text is treated as zero. #N/A will be interpolated for most charts, not Area charts though.

Only truly empty cells will react to the setting Leave gaps, Interpolate or plot as zero.

More info here,
http://www.andypope.info/charts/brokenlines.htm

Thank you kindly. I figured out that my chart type was not a scatter (which it needed to be). This fixed my problem and now I have gaps for the zero values. I appreciate your help on this! Thanks again and have a great day!

Well, what I thought would work didn't. I must have made a mistake.
Disregard the previous victory post! I still need help. Should I used a scatter graph or line graph to chart 4 measly values? The four values represent totals. The values are averages of inputed columns. Sometimes I have yet to input values so there is an error, #DIV/0! error so I have the following function: =IF(ISERROR(SUM(B4:B16)/B25),"",SUM(B4:B16)/B25)

Keep in mind this function is in B24, C24, D24 and E24 (of course the function is changed respectively). I thought that by getting rid of the error, the graph may not chart the blank cells.
b24 will first contain data, then later c24 followed by d24 & e24.
At the end I will have data at all 4 points, but until then I just want values charter that are greater than zero. Make sense? I read something about masking but didn't quite understand. Any help would be much appreciated.

The change to the formula would be

=IF(ISERROR(SUM(B4:B16)/B25),NA(),SUM(B4:B16)/B25)

And for a line chart the line will be interpolated between valid points and no marker will be displayed.

Is this a trend chart? If not then maybe a column chart is more suitable.

Andy,
You're a genius! That fixed it. Have a great weekend! Thanks again!