Charting

[brian.baker13]

Hi

I do a weekly quality chart at work for worker performance. This is a
horizontal chart with a bar for each employee with %age on the X scale
& the Operator name on the Y. I want to include two target lines
vertically on the chart for 1 & 1.75%. So far I cannot do this anfd
have to draw two lines down the chart. I have two types of worker one
about 15 of them with a 1.75% target and about 5 workers who have a 1%
target. Could anybody help please.

Thanks

Brian

 

[Del Cotter]

I do a weekly quality chart at work for worker performance. This is a
horizontal chart with a bar for each employee with %age on the X scale
& the Operator name on the Y. I want to include two target lines
vertically on the chart for 1 & 1.75%.

You have twenty people in total on the horizontal bar chart, so you want
two lines 21 units in length (actually it can be any length, because
we're going to stretch the axis to fit, but this helps keep things
simple)

Create the following table of cells:

Targets Height
1.75% 0
1.75% 21

1.00% 0
1.00% 21

Then select the numbers underneath the word "Height" (don't include the
word "Height") and add them to the chart as a new series. At this point
they'll look like more bars, but don't worry. Select the new bars, right
click, and select "Chart Type". Change the type from Horizontal Bar to
Scatter, sub-type "Scatter with data points connected by lines without
markers".

Now it's a different chart type, with its own Y axis, but it still
shares the same X values as the horizontal bar, so select Source Data in
the Chart menu at the top of the screen, and change the X Values: range
in the new series to be the first column under "Targets" in the table I
had you write previously.

While you're there, you might like to put the cell containing the word
"Targets" in the Name: range. Now the series is called "Targets" instead
of "Series2".

Finally, make sure the Secondary Y axis goes from 0 to 21, and the
Secondary X axis has the same limits as the Primary X axis, the one the
horizontal bar series is on. Otherwise the lines won't be in the right
place.

Your two lines don't have to go all the way from 0 to 21. They could go
from 0-16 for the fifteen workers, and 17-12 for the five. That would
make it graphically clear which bars have which target.

Jon Peltier has more advice on making combination Horizontal bar and
line charts here:

http://peltiertech.com/Excel/Charts/BarLineCombo.html

 

[brian.baker13]

You have twenty people in total on the horizontal bar chart, so you want
two lines 21 units in length (actually it can be any length, because
we're going to stretch the axis to fit, but this helps keep things
simple)

Create the following table of cells:

Targets Height
1.75% 0
1.75% 21

1.00% 0
1.00% 21

Then select the numbers underneath the word "Height" (don't include the
word "Height") and add them to the chart as a new series. At this point
they'll look like more bars, but don't worry. Select the new bars, right
click, and select "Chart Type". Change the type from Horizontal Bar to
Scatter, sub-type "Scatter with data points connected by lines without
markers".

Now it's a different chart type, with its own Y axis, but it still
shares the same X values as the horizontal bar, so select Source Data in
the Chart menu at the top of the screen, and change the X Values: range
in the new series to be the first column under "Targets" in the table I
had you write previously.

While you're there, you might like to put the cell containing the word
"Targets" in the Name: range. Now the series is called "Targets" instead
of "Series2".

Finally, make sure the Secondary Y axis goes from 0 to 21, and the
Secondary X axis has the same limits as the Primary X axis, the one the
horizontal bar series is on. Otherwise the lines won't be in the right
place.

Your two lines don't have to go all the way from 0 to 21. They could go
from 0-16 for the fifteen workers, and 17-12 for the five. That would
make it graphically clear which bars have which target.

Jon Peltier has more advice on making combination Horizontal bar and
line charts here:

http://peltiertech.com/Excel/Charts/BarLineCombo.html

Hi Del

I kind of got there, my chart has diagonal lines?


Thanks for your advice

Brian

 

[Del Cotter]

I kind of got there, my chart has diagonal lines?

Yes, it sounds like you just need to get that X Values range for the
scatter graph series changed over from the range it started out sharing
with the Horizontal Bar range. The latter is a category range and will
never give you the numbers you need. Because each value is read as 1
more than the previous, the lines end up slanted. You need to get that
{1.75%,1.75%,blank,1.00%,1.00%} range in place so the lines can be
properly vertical (also so they can be at 1.75% and 1.0% like you want
them to be.