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?
 
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

Similar Threads

How do I plot "" as empty rather than zero in a chart 2
Excel 2007 charts: want to avoid zero values 1
zero values in bar chart 4
How to eliminate zero values on graphs when formula calculates 0? 4
Cannot find "Plot empty cells as" option in 2007 8
plots sent to to a chart tab do not show plot & I loss the plot 0
How do i not plot values as zero? 3
How do I disable "Plot visible cells only" when it is greyed out? 1

Top