Amongst the many and varied functions provided by Excel (or as far as I know any other spreadsheet) there are none that provide a one step process for linear interpolation, finding the intersection points of lines, or conversion between polar and rectangular coordinates, and related operations.
These functions and more can be found in the spreadsheet: IP.zip which includes open source code for the following functions:
Update 29th March 2011: For the latest version of this spreadsheet, including many additional functions download IP2.zip. See also the latest related blog post.
Intersection Functions
IP
Finds the intersection points of two 2D lines or polylines
=ip(Line1,LINE2,Optional Coordinate, Optional Point no)
“Line1” and “Line 2” are ranges listing the XY coordinates for the two lines
“Coordinate” specifies the ordinate required, 1 = X, 2 = Y
“Point No” specifies which intersection point is required
If “Point No” is not provided IP returns an n x 2 array, where n is the number of intersection points.
If “Coordinate” is not provided IP returns a 1 x 2 array if “Point no” is provided, or an n x 2 array if not.
If “Line2” is a single point IP returns intersection points for a line through this point and parallel to the X axis if XY = 1, or the Y axis if XY = 2
Intersection points of two polylines
INSIDE
Finds if a specified point is inside a closed polyline
=INSIDE(Polyline, Point)
IPLC
Finds the intersection points of a 2D line and a circle
=IPLC(Line, CircleXY ,Radius)
IPCC
Finds the intersection points of two circles
=IPCC(Circle1XY, Radius1, Circle2XY, Radius2)
IPSSS, IPSS
IPSSS finds the 3D intersection points of three spheres
IPSS finds the location and radius of the intersection circle of two spheres,
and the polar coordinate angles of the line connecting the two cenrtres
=IPSSS(Sphere1XYZR, Sphere2XYZR, Sphere3XYZR)
=IPSS(Sphere1XYZR, Sphere2XYZR)
Distance from centre sphere1 to centre intersection circle, radius intersection circle,
and angle of line connecting sphere centres in XY plane and perpendicular plane (radians)
Rectangular to Polar Functions
RtoP, PtoR
Converts rectangular to polar coordinates and polar to rectangular
=RtoP(Rectangular Coordinate range, Origin, Coordinate number)
=PtoR(Polar Coordinate range, Origin, Coordinate number)
Coordinate number 1 2 3
Rectangular X Y Z
Polar R Theta1 Theta2
Theta1 = angle in XY plane
Theta2 = angle in perpendicular plane
Where an origin is given the origin is moved to the coordinates specified
Rotate
Rotates 2D or 3D rectangular axes about any axis
Rotate(Rectangular Coordinate range, Rotation in radians, Axis, Optional Coordinate Number)
Interploation Functions
interp =interp(tablerange, value, column no) Linear interpolation
interp2 =interp2(tablerange, row value, column val) 2 way linear interpolation
loginterp =loginterp(tablerange, value, column no) Log interpolation
loginterp2 =loginterp2(tablerange, row value, column val) 2 way log interpolation
quadinterp =quadinterp(tablerange, value, column no) Quadratic interpolation
Interpolation functions
Newton Excel Bach, not (just) an Excel Blog 
Pingback: Excel Links of Week - Who is Obama edition? [Aug 12] | Pointy Haired Dilbert - Chandoo.org
Pingback: IP.xls updated « Newton Excel Bach, not (just) an Excel Blog
Briliiant solution. I have a function that finds intersections of two polylines but it is not nearly as elegant as yours. Regards, Mike
LikeLike
Mike – thanks for the comments, glad you found it useful.
I have just uploaded the latest version, with the “pdist” function modified to deal with duplicate points.
http://interactiveds.com.au/software/IP.ZIP
LikeLike
I am not sure why excel have never developed a function for finding intersections. I think you should try and sell it to microsoft as it will be a great addition to the standard functions.
One thing that would be useful (maybe if it can be done with existing functions) is to have a separate function (line intersections) that can input data from separate ranges i.e. at the moment as far as I see from your function is that the Line 1 array (and Line 2 array) are input as a single array containing both x and y coordinates. However, I have found that often the x range may not be alongside the y range (i.e. at different locations in the spreadsheet) and it would be useful to input them as separate distinct ranges i.e. for Line 1 a X1 range and Y1 range and for Line 2 a X2 range and a Y2 range. You may be able to do this with existing functions. but I am not that famliar with array commands.
PS I also tried your function that calculates values from formulaes. However, it didn’t seem to work for me – not sure what I am doing wrong – does it work with excel 2007?
LikeLike
Mike, good suggestion, I’ll add that as an option when I have time.
In the meantime the easiest thing is to use worksheet formulas to create a new range with adjacent X and Y values
The eval spreadsheet works in XL 2007, or should do. Do the examples in the downloaded file not work on your machine? If you want you can send me a sample at dougaj4 at gmail and I’ll have a look at it.
LikeLike
Hi Doug, Yes one can modify the spreadsheet to do that but if you have got a number of intersections to determine then it can become unwieldy. I have been trying to modify function to do this but my knowledge about how arrays work is limited so I am not sure about my sucess. Basically I have inserted Line1X, Line1Y, Line2X and Line2Y into the function arguements (replacing Line1 and Line2) and then have attempted to transfer this data into your original Line1 and Line 2 arrays so that there should not be to much affect on the downstream code. Not working as yet will have another attempt tonight (the function output is #VALUE! at the moment, which was an improvement on my earlier attempts.
I will keep an eye on this page in the meantime for any upgrades.
I couldn’t seem to download the spreadsheet for some reason so pasted the code into a module and it didn’t work – not sure what I was doing wrong. However, I have now been able to download the spreadsheet and it works fine. Thanks for that.
Great site.
Regards, Mike
LikeLike
Mike – I have added an IP_4 function in the IP_4.wks spreadsheet you can download from:
http://interactiveds.com.au/software/IP.ZIP
I haven’t tested it thoroughly, so please let me know if you have any problems with it, or if you have any other comments.
With the evaluation function, it looks like you have been hit by the dreaded WordPress quote bug. The WordPress software changes all “” into something that looks the same, but isn’t. If you copy and paste code you have to do a search and replace for all the “”, and replace them with proper quotes. The same applies to apostrophes.
I should have a standard note to append to all posted code. Better still, the WordPress people should fix it.
LikeLike
Hi Doug, Just checked and found your update – thanks for that. I did do some tinkering with your earlier code (with a lttle help) and did manage to get it to accept separate x and y ranges. The code is not as efficient as yours, however, the problem now is that it doesn’t give any results if the line ranges are horizontal. How to get it to work for a combination of horizontal or vertical x, y ranges is beyond my abilities i’m afraid. Need to have some code that transposes the x,y data if it in a horizontal range so the it is in a form for the rest of your code to work.
Best regards, Mike
LikeLike
Mike – I have added a transpose function, that transposes the range if there are more than 2 columns wiith IP() or more than 1 column with IP_4().
With IP_4 it checks both lines, but assumes the Y data for each line has the same orientation as the X data. Is that a problem?
Actually I’ll amend it to check both X and Y later; probably by tomorrow.
LikeLike
Hi, Doug, You’ve been very tolerant. The function is going to be very useful. I do a lot work with data that requires interpolation. As I said I have a function that does do this but it is not as good as yours as it only finds one intersection point. I use it regularly and has been a very useful function. I will be replacing my existing function with yours. I’ve been searching the net regularly for a similar function and have only ever seen very basic functions that do this. Will keep an eye on your web page for the update.
Regards, Mike
PS I see that you live in Sydney – I’m in Brisbane – It’s pouring down with rain at the moment. Looks like easter may be wet.
LikeLike
Mike – I appreciate getting the feedback, and I enjoy playing with these things anyway. Glad you found the function useful.
That’s Queensland, beautiful one day, besodden the next :).
(Sydney has been raining every day for the ast week, and another week to come by the way)
LikeLike
I think the rain is here to stay until well into Easter. I’ve been trawling the web lately for excel code and that is how I find your site. Its amazing what you can find. I also found an excellent method for allowing you to set max and min values on x and y coordinates along with titles and chart types using functions. Always used macro’s in the past to do that, however, using functions is much better as it is automated and much simpler as you can call it from anywhere and it does not require a macro button, etc.
LikeLike
Pingback: Another update to IP.xls « Newton Excel Bach, not (just) an Excel Blog
Hi Doug, I made a few changes to your code to allow line1 ranges to be either in columns or rows eg line1x in rows and line1y in columns, etc.
Have also adjusted function it so it can find intercepts on vertical or horizontal lines by simply inputing the x coord for vertical lines. and y coord for horizontal lines. This saves having to make worker columns in the spreadsheet. I use that a lot in determining intermediate populations in projections, etc
The last change that I made was including a comment of “no intercept” when that was the case (at the moment) it outputs 0 which may actually be correct.
It took me a while to work that all out but it was fun. I am sure that you would have done it much quicker. I can email you the file with the changes that I made. I am sure that you would have done it in a more efficient method.
All the best, Mike
LikeLike
Mike – if you’d like to send your spreadsheet to my gmail address (dougaj4 ), I’d be very interested to see it.
LikeLike
Hi Doug, I emailed the spreadsheet earlier. Hope that you got it. Mike
LikeLike
how to create y axes, y1axes,x axes
LikeLike
Mani – can you give more details of what you are wanting to do?
LikeLike
Hi Doug –
The link to IP.zip appears broken.
…mrt
LikeLike
Michael – thanks for letting me know.
I have fixed the link, but note that the latest version (IP2.ZIP) is now at:
https://newtonexcelbach.wordpress.com/2009/08/18/finding-circle-centres-in-3d/
Doug
LikeLike
Hi Doug –
Thanks. I went and got v2.0.
Have you looked at Euler #246? It looks right up your alley as a geometry problem. It’s been stymieing me for a while, and I was thinking your intercept routines might help. I want to solve simultaneous equations, but keep stumbling over the quantity of the slopes of the tangent of an ellipse. My premise is to set the slope of the line equal to the slope of the tangent, or m of the line equal to dy/dx of the ellipse. Ought to be doable, but I’m not seeing the way. Everything I’ve tried is either circular or assumes I know the value ;-(
It’s the highest number I’ve attempted.
…mrt
LikeLike
Hi! good day!! I am working in a very similar project, waht i have is this:
* i have a Line-graph (line with markers). It graphs time vs a value
* the user will draw a shape (a FreeForm), and i want to get the points of my graph that are under the area of my shape.
Thanks a lot already for any possible help on this matter!
LikeLike
Gus – you will need to get the coordinates of the graph line and the freeform shape with a common frame of reference. My posts on drawing in Excel (for example https://newtonexcelbach.wordpress.com/2008/09/17/drawing-in-excel-6-getting-shape-properties/) should give you enough information to get the screen coordinates of the shape. I’m not sure if the screen coordinates of the chart line are easily available or not, and I don’t have time to look into it at the moment. Jon Peltier’s site would be a good place to start looking for that, or a question on one of the big Excel forums.
Once you have got the screen coordinates you could either use the IP function to find the crossing point(s), or the Inside function to find those points outside the shape.
If you find a solution I’d appreciate it if you could post it here.
LikeLike
Hello
Congratulations on this task,
Please help me with the following problem …
can’t get good results
for example, given the following data :
Plane1 0,000 0,000 0,000
Plane2 14,000 0,000 2,000
Plane3 0,000 3,000 0,000
Line1 1,000 1,000 -1,000
Line2 0,500 0,500 1,000
PointIntersc(IP2) 0,741935484 0,741935484 0,032258065
PointIntersc(Autocad) x=0.7241379 Y=0.7241379 Z=0.1034483
graphically, the right is Autocad…
I’m doing wrong? please
Thank you,
LikeLike
Miguel – Thanks for letting me know. I get the same results. I’ll look into it and when i get time and post results here.
LikeLike
Miguel – you can download a corrected version at:
htttp://www.interactiveds.com.au/software/IP2.ZIP
I will write it up some time in the next few days, but I’d be grateful if you could have a look and confirm that you agree that it is now OK.
LikeLike
Pingback: IP2 Update, ByRef and ByVal | Newton Excel Bach, not (just) an Excel Blog
I’d like to graph intersections, without connecting lines. For example, I need to graph the intersecting points for 4 sectors:
Sector x, y
North 50, 50
South 60, 40
East 30, 70
West 10, 90
where the x-axis is number of strategic initiatives, y-axis is number of operational initiatives. The range of each of the axis should be 0 to 100.
seems like it would be very simple to do in excel, but I don’t know how to do it. PLEASE HELP! TIA.
LikeLike
Karla – I don’t know what intersections you want to plot. The points you gave are all on the same straight line. A sketch would be helpful. You can e-mail to dougaj4 at gmail.
LikeLike
Would it be possible to use the IPSSS Function to do 2d Trilateration with provided GPS decimal lat long circle centres and a specified radius for each circle. If possible, how can it be done
LikeLike
Petrus, could you provide details (or a link) of exactly what you mean by 2d Trilateration.
Thanks
LikeLike
Pingback: Anonymous
Pingback: Daily Download 16: Intersections, interpolations, and rotations | Newton Excel Bach, not (just) an Excel Blog
Hi Doug,
hope you get to see this message, i’d really appreciate your help. I need a function that tells me if the points I introduce are within a 3D (x,y,z) surface area. I guess function Inside3D might be useful but I am not sure how to use it, could you tell me if i’m correct?? I basically need to make a surface out of several 3D coords and when introducing a point, function must tell me if its inside the (x,y) coord. of surface and either above or below (z) the surface. Thanks anyone who might be able to help!
LikeLike
Ok, Inside 3D is what I’m looking for, except I want it to consider only x and y coord. (2D surface, as in Inside function) when calculating if its inside/outside and the Z coordinate to know if its wither above or below. Let me set an example. Say I have the following 3D plane;
(0,0,0)
(0,4,1)
(4,4,3)
(4,0,1)
(0,0,0)
and a point with (2,2,8)
In this example, Inside function would tell me its inside (only x&y coord. taken into account), while Inside3D would say its outside, as it is designed for it. How could I get it to tell me it is Inside and, in this example, ABOVE the plane?? cheers
LikeLike
The Inside3D function is only intended for points that lie on the same plane as the other points. In your example your four points actually define two different planes, but if you draw a line from 2,2,8 perpendicular to either one of the planes it passes outside the rectangle defined by the four points, so on that basis the point is outside your box.
LikeLike