Complicated question re: adding extra horizontal lines

  • Thread starter Thread starter Meenie
  • Start date Start date
M

Meenie

Excel 2003
I have a standard clustered column chart and it shows numbers (in time)
gathered for several different groups each month (so group A has bars for Jan
- June [eventually - Dec] then group B, etc)
I have put a horozontal line across denoting the target number for all of
them (same each month so it's one line across)
I would like to add another horozontal line for EACH group showing it's YTD
average. So t his line wouldn't go across the entire chart, only across the
section for that particular group, but I don't know how to do that. I figured
it would be something to do with where the line starts and stops on the x
axis?? How can I do that? Or is it do-able?
thanks, Meenie
 
Hi,

Don't know quite how your data is laid out but

You could enter a formula like =AVERAGE(A$2:A$7) and copy it down for all of
one group, then create a similar formula for a second group say,
=AVERAGE(B$2:B$7)which averages its values.
Plot these extra ranges on you chart. Each range will be a straight line.

If we know the data layout we might suggest using AVERAGEIF in 2007 or an
array formula in 2003.
 
Here's how it's laid out:
Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec
Group
1 0:06:34 0:05:17 0:05:17 0:04:23 0:03:43 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00
Group
2 0:01:49 0:01:39 0:01:38 0:01:54 0:01:46 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00
Group
3 0:02:34 0:02:06 0:02:37 0:02:39 0:02:18 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00
Group
4 0:04:43 0:04:02 0:03:25 0:03:26 0:03:13 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00
Group
5 0:02:57 0:02:35 0:03:09 0:02:33 0:02:28 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00
Group
6 0:01:25 0:01:39 0:01:17 0:01:14 0:01:03 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00
Target
0 0:02:30
1 0:02:30

It already has a horozontal line for the Target number.
when I try to add another horozontal line, it automatically puts it across
the whole
chart but I want it just inside the section for each group because I want to
show the average for that group.
How can I make the line only appear within that one section?




Shane Devenshire said:
Hi,

Don't know quite how your data is laid out but

You could enter a formula like =AVERAGE(A$2:A$7) and copy it down for all of
one group, then create a similar formula for a second group say,
=AVERAGE(B$2:B$7)which averages its values.
Plot these extra ranges on you chart. Each range will be a straight line.

If we know the data layout we might suggest using AVERAGEIF in 2007 or an
array formula in 2003.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Meenie said:
Excel 2003
I have a standard clustered column chart and it shows numbers (in time)
gathered for several different groups each month (so group A has bars for Jan
- June [eventually - Dec] then group B, etc)
I have put a horozontal line across denoting the target number for all of
them (same each month so it's one line across)
I would like to add another horozontal line for EACH group showing it's YTD
average. So t his line wouldn't go across the entire chart, only across the
section for that particular group, but I don't know how to do that. I figured
it would be something to do with where the line starts and stops on the x
axis?? How can I do that? Or is it do-able?
thanks, Meenie
Click to expand...
Click to expand...
 
Back
Top