Excel: Adding a Connector Line In a Chart (Have to Use a Macro)?

So I'm working on this chart in Excel, and the chart looks like two sides of a triange, like the picture at the link below:

http://a.imageshack.us/img832/6207/triangle.png.

I'd like to make a line (like, with an autoshape for example) that connects the 2 endpoints to form a triange; ie a line going from coordinates (4,1) to (4,5). I tried doing this by creating a seperate data series in excel with the coordinates of the connecting line, like:

4,1 4,2 4,3 4,4 4,5

However, in excel, all lines in this chart must have the same x coordinates. Does anyone know how to get a connecting line automatically? Say, with a macro or something? Reason I ask is because I want to be able to change these coordinates, and I want the connecting line to adjust with very little effort (ideally I could write a macro and assign it to a button that when you click it would adjust the connecting line). I have some very limited macro experience, but this is just beyond me. The type of chart I'm using is a standard line chart, where the description given by excel is "Displays trends over time or categories". Anyone know of a good resource or have some advice? Thanks.

PS I solved this problem by changing the chart type to a scatter chart, but this messed up th开发者_JS百科e axes and scale, and required that those be adjusted, so I think adding a line in this manner automatically will be easier.

The scatter chart worked OK.
Used formulas included in the following image.
Perhaps you should include an image showing the problems you found.