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:
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
Filed under: Coordinate Geometry, Excel, UDFs, VBA | Tagged: Excel, Interpolation, Intersection Points, Polar Coordiantes, Rectangular Coordinates, UDF, VBA
[...] Excel UDFs for advanced linear interpolation, conversion of co-ordinates from rectangular to polar If you use excel to do graph related analysis, to build 2d / 3d models then this set of downloadable UDFs by Newton Bach’s blog would be very useful to you. [...]
[...] Posted on February 16, 2009 by dougaj4 The spreadsheet IP.xls has been updated (previous post) with the addition of a function to find the minimum perpendicular distance from a point (or [...]
Briliiant solution. I have a function that finds intersections of two polylines but it is not nearly as elegant as yours. Regards, Mike
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
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?
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.
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
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.
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
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.
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.
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)
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.
[...] Comments Drawing in Excel … on Drawing in Excel - 2Mike Seymour on Intersections, interpolations,…Gaggriema on Writing an array to a workshee…dougaj4 on Intersections, [...]
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
Mike – if you’d like to send your spreadsheet to my gmail address (dougaj4 ), I’d be very interested to see it.
Hi Doug, I emailed the spreadsheet earlier. Hope that you got it. Mike
how to create y axes, y1axes,x axes
Mani – can you give more details of what you are wanting to do?
Hi Doug -
The link to IP.zip appears broken.
…mrt
Michael – thanks for letting me know.
I have fixed the link, but note that the latest version (IP2.ZIP) is now at:
http://newtonexcelbach.wordpress.com/2009/08/18/finding-circle-centres-in-3d/
Doug
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