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
