Thread: Plotting 0 Values

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[INDENT] 37.0 37.0 37.0 37.0
Actual 38.5 39.3 36.4 35.8 39.3
Forecast[INDENT] 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

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/E...issingData.htm
http://processtrends.com/pg_charts_missing_data.htm

Cheers
Andy

gailb14 wrote:
> 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[INDENT] 37.0 37.0 37.0 37.0
> Actual 38.5 39.3 36.4 35.8 39.3
> Forecast[INDENT] 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
>
>

--

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