Create Margin lines in Excel 2003 chart

[Protheus]

I need some asistance on the following issue>

I have a table and from that I want to create a chart - The chart creation goes well.

ex:
1 10
2 11
3 24
4 13
5 14
6 36
7 50
8 17
9 10
10 19
11 13

So the above table is created in a chart and the chart draws excellent. Now what I want to do is that I want to create two horizontal lines so that I can see where the marges lies.

So I want to have a horizontal line at approx 45 and one at 20 so that I can see which point in the charts are within those margins.

I have looked everywhere in excel 2003 and I am not able to do so.. so Can someone point me in a direction to look at at thisis really getting on my nerves

 

[Protheus]

I created the chart in lines format and I want in there the two horizontal lines

 

[KenWright]

Add two more data series, tied to 2 single cells each containing 20 and 45 respectively. Then just include these within your chart ranges

A B C
1 10 20 45
2 11 20 45
3 24 20 45
4 13 20 45
5 14 20 45
6 36 20 45
7 50 20 45
8 17 20 45
9 10 20 45
10 19 20 45
11 13 20 45

Each cell in Col B is the same formula, eg =$H$1 with a value of 20 in H1.

Each cell in Col C is the same formula, eg =$H$2 with a value of 45 in H2.

Include these within your chart ranges, format them as line graphs (just these two series) and then by changing the data in cells H1/H2 you can have the lines anywhere you want.

Regards
Ken.............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Protheus]

Excellent workaround !!! me very happy - tnx again

 

[KenWright]

You're welcome. You can also if you so wish tie those two trigger cells to the existing data with formulas, such that if you wanted the lines to represent points at say 25% of your data, then each cell could simply be a formula that said 'Average of Data / 2' to get a lower end, or 'Average of Data * 3/2' to get an upper end, eg if cell H1 instead of having a hardwired value of 20 actually had the folowing in

=ROUND(AVERAGE($B$3:$B$13),0)*3/2

and H2 instead of 45 had in

=ROUND(AVERAGE($B$3:$B$13),0)/2

then you would get the following:-

A B C
1 10 10 30
2 11 10 30
3 24 10 30
4 13 10 30
5 14 10 30
6 36 10 30
7 50 10 30
8 17 10 30
9 10 10 30
10 19 10 30
11 13 10 30

As your data changed, then so would your lines. You could obviously put anyt formula you liked in that would make sense from what you wanted to appear on the chart, and it would then be automatic.

Just a thought.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------