Plotting 0 Values

[gailb14]

I have a combination bar and line chart.

Two series, Target and Actual are bars, the other, Forecast is a line.
The following is an example of my data.
2005 Actual Jan Feb Mar Apr
Target

37.0 37.0 37.0 37.0
Actual 38.5 39.3 36.4 35.8 39.3
Forecast

37.3 37.6 37.9 38.4

I have tried several ways to plot the chart. With and without the 200
Actual for Target and Forecast. I either get no 2005 actual data or th
2005 line includes points for Target and Forecast. It is okay for th
bar (Target) because if the point is 0 there is no bar, however for th
line data (Forecast) it starts at 0 and has a steep incline up to 37.0.
I want those two data points to start in Jan. Can you help. Thank​

 

[Andy Pope]

Hi,

If the cells are truly empty the no line point should be plotted.
If the cell contains a formula then use NA() instead of "".

Here are a couple of pages on the subject.

http://www.stfx.ca/people/bliengme/ExcelTips/MissingData.htm
http://processtrends.com/pg_charts_missing_data.htm

Cheers
Andy

I have a combination bar and line chart.

Two series, Target and Actual are bars, the other, Forecast is a line.
The following is an example of my data.
2005 Actual Jan Feb Mar Apr
Target

37.0 37.0 37.0 37.0
Actual 38.5 39.3 36.4 35.8 39.3
Forecast

37.3 37.6 37.9 38.4

I have tried several ways to plot the chart. With and without the 2005
Actual for Target and Forecast. I either get no 2005 actual data or the
2005 line includes points for Target and Forecast. It is okay for the
bar (Target) because if the point is 0 there is no bar, however for the
line data (Forecast) it starts at 0 and has a steep incline up to 37.0.
I want those two data points to start in Jan. Can you help. Thanks
​

​