Connect two line graphs?

[3G]

Hi there-

I have 2 sets of data/series...Actuals & Projected. What I'm trying to do is have 1 line on the graph where Actuals show Jan-Aug, then, Projected shows Sept-Dec. For every actuals month, I've set the projected values to 0, but, at the aug/sept junction the graph spikes way up and there's a gap between the two lines. How can I connect them? The lines are two different colors, and I'd like just 1 line with no break.


Thanks

3G

 

[Luke M]

You need to have a common point so the lines connect. Ie, actuals needs to have something for months Jan - Aug, but then estimated needs to go from Aug to Dec.

 

[fred]

I use one column of data where Jan-Aug is actual and Sept-Dec is Expected. Plot the graph with just one column of data, not two. change the line color from Sept to Dec into a different color and make it dotted line.

 

[Hui]

3G & Fred

The technique Luke suggested is a much better way to do this

Use 2 series 1 for Actuals and 1 for Forecast

The last point of Actuals must be the same as the first point of Forecast

with the remaining points in both series having a na()

Doing this allows the 2 series to have different colors and linestyles and allows the charts to automatically update each month.

so your data will look like

[pre]

Date	1-Jan	1-Feb	1-Mar	1-Apr	1-May	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct	1-Nov	1-Dec
Actual	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	17	11	11	20	17
For't	18	12	18	18	14	17	19	17	#N/A	#N/A	#N/A	#N/A

[/pre]

 

[3G]

Yes! Looks great Luke & Hui. Thanks for the help. Also, thanks Fred for the suggestion too!

 

[Kamarlon]

Hui: Can this be done using name ranges?

 

[Kamarlon]

I don't know how to tell excel that for the last value in the actual column the forecast column must have that same value too.