Line Chart Format (Actual vs. Forecast)

[sahafi]

I have a line chart with weekly data for 2 years. Weeks on the X axis and
Pounds on the Y axis. They represents actual and forecasted pounds. I need to
show the first portion of the line (actual) with solid color while the rest
of the line (forecast) with dotted line. We are talking about one single line
for production. How can I accomplish this? I don't want to do it manually.
I'd rather have it changes automatically every week when the actual data
override the forecasted data.

Thanks.

sahafi

 

[Jon Peltier]

Make the chart with two series. Put all the dates in column A, put the
actuals in column B down to some date with blanks below, put the forecast in
column C from that date downward with blanks above. Plot this data, format
the two series the way you want them to appear (one solid, one dashed). As
you add an actual value, delete a forecast value. The chart keeps up
automatically.

- Jon

 

[sahafi]

Thanks Jon. That trick worked. But one thing, how can I connect the two lines
together? my actual goes down to week 12 and my forecast starts on week 13.
On the graph there's space between the points 12 and 13. I have tried to move
the forecst data up one row to start at 12, but that didn't help either.

Thanks.

 

[Jon Peltier]

At the X value where the curves meet, include a Y value for both series.

- Jon

 

[sahafi]

Adding a 'Y' value to the forecast data only worked better than adding the
same value to both actual/fcst.

Thanks for the help.

 

[John Scola]

Hello,

I am doing something similar to what Sahafi is doing except I am doing monthly budget data. My chart data looks as follows...

Col A Col B Col C
Oct 1000
Nov 3000 3000
Dec 4000
Jan 5000
Feb 6000
Mar 7000
Apr 8000
Jun 9000
Jul 10000
Aug 11000
Sept 12000

The numeric data is gotten via paste links to cells in a different sheet within the same workbook that use VB functions to calculate the cell values. However, when I go to plot the data, my x-axis for the months is cut short and I do not see a line for the data in column B. Any ideas what might be going on?

 

[Jon Peltier]

Your X axis is "cut short" how? I simply selected the data and created a
chart, and I got months Oct thru Sept (except for May, missing in your
data), with one line from Oct to Nov and the other from Nov to Sept.

- Jon