Baseline values for comparison

[Carlee]

Hi All,

I am new to excel, so please bare with me.

I have a chart that displays treatment values collected for each month. The
treatment value is the Y Axis, and the dates for a specific month is the X
axis.

Question: I have a budgeted value for each month that is set at the
begining of the year and stored in my workbook. What I want to do is display
the Budgeted value as a horizontal line, whereby the user can view where the
actual value in comparison to the budgeted value.

I have been able to plot the budgeted value as a single point, but cannot
seem to get it to display as solid bar on the graph.

Any ideas?

 

[John]

create a helper column with that value in it for the number of points you
need and add that to the chart as a set of data
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Carlee]

Hi there,

What is a 'helper column', and how would i go about achieving this? I need
to show the baseline value as a horizontal bar, not a veritical one.

Could you assist a bit further?

 

[Del Cotter]

What is a 'helper column', and how would i go about achieving this?

Basically, when most people want to use an Excel spreadsheet for
graphing, they expect to be able to type their data into a minimum
number of cells and then go and make a chart from it. Any translation
from data to chart, they expect to do using the chart application.

It usually doesn't work like that, bu fortunately, the tiny number of
cells used still leaves an enormous number of cells available to turn
the data into something that the graphing utility can use. The range of
spreadsheet cells filled with formulas that turn your data into a
graphable range is called, in Excel chart users' jargon, a "helper"
range, because it helps the data to be graphed.

I need to show the baseline value as a horizontal bar, not a veritical
one.

You need at least two X values and two Y values to achieve this (if you
make the line an "XY (Scatter)" chart type), or as many Y values as
there are values in the X range (if you make the line a "Line" or
"Column" chart type. I assume you have chosen a line chart for your
basic treatment data, and so we'll stick to that for the budget line.
Here's what your treatment range probably looks like:

Treatment
1 10
2 12
3 10
4 12
5 10
6 12
7 10
8 12
9 10

Let's say your budget is 11, so you probably typed a single "11" in a
column next to the treatment data:

Treatment Budget
1 10 11
2 12
3 10
4 12
5 10
6 12
7 10
8 12
9 10

This did you no good, because, being only one value, it only showed up
as a single dot on the graph. What you should have is something like
this:

Treatment Budget
1 10 =$A$1
2 12 =$A$1
3 10 =$A$1
4 12 =$A$1
5 10 =$A$1
6 12 =$A$1
7 10 =$A$1
8 12 =$A$1
9 10 =$A$1

....and then put "11" into the cell A1 (just an example, choose a cell
that suits you). All the cells in the range will refer to A1 and have
the value you choose for the budget. If the budget changes, you just
type a new value in, and all the formula cells will change their value
to match. Now, because the helper range is as long as the data range, it
runs in a single horizontal line all the way across the chart.

You may be annoyed that the line is thin, and punctuated with symbols
all the way along. You can fix that by right clicking on the line,
selecting "Format Series", and changing the Markers to "None" and the
Line to "Thick". Then you should be set.