multiple columns, each with a forecast line

  • Thread starter Thread starter James Nell
  • Start date Start date
J

James Nell

Hi
i'm trying to build a chart that will display actuals (columns) and
forecasts (which is a horizontal line about the same width as the column
within the column)

x-axis - product a, product b, product c
y-axis - sales

for each column (which denotes a product), I would like to plot a horizontal
line to indicate the forecast. The column height indicates actual, and the
line height indicates the forecast.

any help would be most appreciated

thanks
K
 
Making a horizontal lines the same width as the columns in the chart
could be a task, but this might be a useful workaround. Sample data:

Actual Forecast
a 500 300
b 400 400
c 300 500

Make a column chart, format Actual with a fill color but no border, and
Forecast with a border but no fill. While formatting one of the series,
click on the Options tab and set Overlap to 100%. The columns line up,
and if Forecast is the second column, it plots on top of Actual.

Another option is to make your column chart as above, right click on the
Forecast series, choose Chart Type from the pop up menu, and select XY
Scatter. If Excel gives you secondary axes, right click on the chart,
choose Options from the pop up, click on the Axes tab, and clear the
checkboxes for any secondary axes. Double click the Forecast series,
format the Patterns to be no line and no marker, and add X error bars in
both directions with a value of 0.2 (which works with the standard gap
width on the column series of 150%). Double click the error bars, and
choose whatever color you want, and choose the style without the cross
bars on the ends.

- Jon
 
Back
Top