Missing values in Excel Line Chart

[mw55309]

Hi
How do I handle missing values in an Excel chart properly? As an example,
if we have values for series A which are 3,4,missing,6 and series B which are
4,missing,6,7 and I want to draw a line graph for these two. For series A
there should be a continuous line through points 3,4 and 6 and for series B
there should be a continuous line through points 4,6 and 7. Excel does not
do this - it either draws a broken line, or sets the missing values to zero.
But the values are not zero, nor are they missing - I simply did not take
measurements on those days.

Help?

[Andy Pope]

Hi,

If the cells are truly empty the select the chart and then use the menus
Tools > Options. Select the Chart tab of the dialog.
There is an option to "Plot empty cells as:", you need the Interpolated
option.

If the cells contain formula then use NA() when there is no data to be
displayed.

Cheers
Andy

mw55309 wrote:
> Hi
> How do I handle missing values in an Excel chart properly? As an example,
> if we have values for series A which are 3,4,missing,6 and series B which are
> 4,missing,6,7 and I want to draw a line graph for these two. For series A
> there should be a continuous line through points 3,4 and 6 and for series B
> there should be a continuous line through points 4,6 and 7. Excel does not
> do this - it either draws a broken line, or sets the missing values to zero.
> But the values are not zero, nor are they missing - I simply did not take
> measurements on those days.
>
> Help?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info