How do I not plot zero values that result from a formula in Excel?


T

tva11

I have a data table with two rows.
One row calculates a percent complete value from the other row. The data is
monthly. I am charting the results.
The percent complete for a month is blank until I fill in the data for the
month.
The chart is plotting the blank percent complete as a zero.
I set the chart options already to not plot blank cells, but I'm guessing
since there is a formula in there it doesn't think that the cell is blank.
What can I do?
 
Ad

Advertisements

J

Jon Peltier

The problem is that "" looks like a blank, but Excel does not treat it as a
blank. Excel treats it like any other text, and assigns it a value of zero.

Unfortunately Microsoft has not provided a function that returns a blank.
The best we can do is use NA(), which places an #N/A error in the cell. Line
and XY series do not plot a point if a cell contains #N/A.

Your formula should look like

=IF(<something>,<value>,NA())

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top