Two line graph

[Duncs]

I have two values, Internal & External. Both of these are scores and I want to plot them on a graph so that Internal is the Y-Axis value, with the line going right. I also want to plot External on the X-Axis, with the line going up. The idea is to find the intersection point of the two lines.

Anyone any ideas?

Duncs

 

[Oaktree]

Welcome to the board!

You can probably get there with error bars as in Jon's example here:

http://peltiertech.com/Excel/ChartsHowTo/DropLines.html

 

[Steve=True]

This can also be done using an XY Scatter Line Chart.

Set up your data like this:

............colA.......ColB...ColC...ColD
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=64>Row1.....External.....X's.......1.....1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>Row2.....External.....Y's.......0.....1.25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>Row3.....Internal.....X's........0.....1.25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>Row4.....Internal.....Y's........1.....1

</TD></TR></TBODY></TABLE>
Click on a blank cell not adjacent to the data above to create a blank chart. If you highlight the whole range, Excel does not interpret the data correctly for the XY Scatter Line

Insert a XY Scatter Line Chart

Add 2 Series to the chart

1 for external
..........x's =Sheet1!$C$1:$D$1
..........y's =Sheet1!$C$2:$D$2

2 for internal
..........x's =Sheet1!$C$3:$D$3
..........y's =Sheet1!$C$4:$D$4

You now have 2 lines starting at zero and intersecting at Point (1,1) and extending to 1.25.

Steve=True
www.exceldashboardtemplates.com

 

[Duncs]

Steve,

Struggled with where to put the score values in, but got there in the end. Really useful graph. Many thanks for your help.

Duncs

 

[Duncs]

I'm using Excel 2007, and had difficulty in following the instructions on the site. The suggestion by Steve was easier to follow, and gives scope for alteration. Perhaps the details on the link you provided would do the same, but as I say, I found them difficult to follow.

Many thanks for your help though.

Duncs

 

[Steve=True]

Glad it worked for you Duncs!

Steve=True
www.exceldashboardtemplates.com

 

[Steve=True]

I have posted a more robust step-by-step tutorial and video on how to do this and also how to add an intersection point.

There is also a sample download file.

The tutorial shows you how to do this dynamically by entered values.

check it out.

http://www.exceldashboardtemplates....zontal-line-that-intersect-in-an-excel-chart/