MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Line Charting


Posted by Johnna Hogue on January 10, 2002 1:12 PM

I am trying to create a line chart using information that is calculated by a formula. I want to set up the chart so it will show Jan - Dec of the given year and only plot the data that has been calculated - ignoring the cells that have no data in them. I have set up "IF" statements in the cells that calculate telling it to do the calculation if data has been entered in another cell, if not, it puts a " " (blank) in the cell.

Unfortunately, Excel reads the blank as a zero and plots this on the chart.

Is there a way to tell the chart to treat blanks as no data and not plot anything?

I don't want people updating the information on the spreadsheet to have to update the charts.


Posted by Damon Ostrander on January 10, 2002 1:46 PM

Yes, Johnna. Just select your chart and then go to Tools -> Options, and to the Chart tab. Select the plot empty cells as: not plotted option.

Damon

Posted by Dan on January 10, 2002 1:50 PM

If you want to have no plotted data and have the previous plotted point join with a line straight to the next available plotted point, replace "" in your formula with NA() like:

=If(A1=6,A1+B1,NA())

See if that does what you are looking for.

Posted by Dan on January 10, 2002 2:05 PM

Damon: that actually does not work with formulas.

At least it didn't for me when I tried duplicating her problem.

Posted by Mark W. on January 10, 2002 3:23 PM

Or, replace "" with #N/A

Posted by Damon Ostrander on January 11, 2002 1:46 PM

Re: Damon: that actually does not work with formulas.

Dan,

Right you are. Your NA() solution is the one!

Damon