Copy the following Formulae, 1000 times to the right!

[stockgoblin42]

Hi,

I have a pair of formulae that I'd like to copy 1000 times to the right. Please see workbook. The problem is, the change in them is not the usual. Okay, the first pair of columns (labelled 1,1 in yellow) calculates the x and y value of an intersection point using the slopes and y-intercepts from that row and the row below it. The 2nd pair of columns (labelled 2,2,) calculates the x and y values of an intersection point using the slopes and y-intercepts from that row and the row 2 below. The 3rd, 3 below, the 4th, 4 below. How do I copy this for 1000 pairs of columns to the right? see workbook please


Thanks for any help

[Pete_UK]

Change your formula in I2 to this:

=(INDEX($B:$B,(COLUMN()-5)/2+ROW()-1)-$B2)/($A2-INDEX($A:$A,(COLUMN()-5)/2+ROW()-1))

Then when this is copied across (in pair with the adjacent cell) and down it should give you the references that you want.

Hope this helps.

Pete

[stockgoblin42]

Hey Thanks. That works great.

I now have all the line intersections for 1000 lines. Now I want to get all the intersection points that match exactly. Unfortunately, the intersection has two values. Is this possible?

[shg]

You could instead use a formula that calculates the Euclidian distance between pairs of intersections. Then you just need to test for values < some epsilon.