missing data and line graph

[Charmed]

Hi, I have typed #N/A in cells where data was not available.
Now I want to pull a chart but I want the line chart to not show the missing data but show a continuous line.

I know it can be done but I can't recall exactly.

Thanks in advance.

Rebecca

 

[Datsmart]

This issue is explained here:
http://www.tushar-mehta.com/excel/software/na_discontinuity/index.html

In chart source range, set zero's to show NA() instead of "" or 0.
You will see N/A# in your table but they will not show or plot in the chart or chart table.
Use a formula similar to this…
=IF(C35=0,NA(),C35)

 

[Charmed]

Thanks so much for your help regarding this. I will proceed to follow what you said. It seems pretty straight forward. If I have any troubles I'm sure I'll have to ask you another question. But it seems okay.

Thanks again.

Rebecca

 

[Charmed]

HI, I'm trying to do the above but I don't have any experience entering formulas into the chart source area. I know how to select a source range but where do I put the formula?

Thanks in advance!

 

[Jon Peltier]

The formula goes in the cells that contain the data. The chart source can only contain linking formulas, not calculating formulas.

If you already have #N/A in the cells, it should let the line continue over the gap. Did you try it?

 

[tusharm]

If you typed in #N/A, or =NA(), XL should automatically draw a line through the missing data point. What is it doing now?

Alternatively, if you leave the cell blank (no formula, no text, no nothing), you have a choice of how XL treats missing data. Create the chart, then select the chart and select Tools | Options... | Chart tab. In the Active Chart section, set the 'Plot empty cells as' option to 'Interpolated'.

Hi, I have typed #N/A in cells where data was not available.
Now I want to pull a chart but I want the line chart to not show the missing data but show a continuous line.

I know it can be done but I can't recall exactly.

Thanks in advance.

Rebecca

 

[Charmed]

Solved problem

Hi I solved the problem. You're right...I didn't need the formula. Human error was involved.

Charmed

 

[tusharm]

Glad to be of help.