Removing Blanks in Charts (2007)

Topic

New Reply

I’m using an IF statement to copy numbers from one column into another based on the set conditions.

=IF(OR(D3>I3,D3<H3),D3,"")

The formulas works fine but when I graph it, cells that fail the test contain "" but plot as zero. Adjusting the HIDDEN and EMPTY CELLS option to: SHOW EMPTY CELLS AS: GAPS still plots zeroes.

Is there any way to not plot the blanks??? This is part of a template that needs to be operator insensitive. No manual solution (ie: copy, paste special, values)

Reply | Quote

Replies

Instead of a null use a #NA error
=IF(OR(D3>I3,D3<H3),D3,na())

This will be ignored by the chart and the point will be interpolated if a line is drawn.

As you notice a null (or any text, or error other than #NA) will be plotted as a zero on a chart. There is no way to break the line with a formula, you can only do that with a blank cell.

Steve

Reply | Quote

THANX, worx grate.

Reply | Quote