Math help: intersection of two circles? - Tilted Forum Project - TFP - Sexuality, Philosophy and Political Discussion

twistedmosaic · 01-21-2008, 01:53 AM

So, as a part of a larger excel 'program' I'm writing, I need a formula that will let Excel find the intersection of two circles when given their radii and the x and y coordinates of the center. It does not have to check for an intersection--in the range I'm using it for, they will always intersect. Also, they will always intersect at two points.

Any ideas? The best sources I've found on the web are:

http://local.wasp.uwa.edu.au/~pbourke/geometry/2circle/

and

http://www.sonoma.edu/users/w/wilson...s/default.html

but neither of them seem to lend themselves handily to easy translation into a single cell (or small set of cells) in Excel. Ideally I want this to be something nice and concise, so that I can automate finding these intersections with large sets of data, as a part of an optimization exercise.

TIA, any help is appreciated

allaboutmusic · 01-21-2008, 04:03 AM

I'm not going to be much help - I'd have done it the same way described.

I would find the distance between the two intersection points, get the area of the two sectors (defined by the radii of the two circles and the arcs between the two intersection points), and subtract the area of the two triangles (defined by the radii of the two circles and the chord between the two intersection points).

I have a feeling some optimisation can be done based on finding the length of the chord, and using basic trigonometry to simplify the formula to find the area.

Hain · 01-21-2008, 07:16 AM

Well you are going to have to make some compromises, unless you really want to work on the algebra. And small number of cells is arguable.

I went at this from scratch, doing essentially what the first website describes. Attached is a ZIP with the XLS inside it. Some differences between mine and the site's is that I have more intermediate steps, hopefully allowing you see get where it is all coming from. Also, I use P+Q=D (the distance between the centers).

UPDATE: You can rearrange your chart so that no one has to see the intermediate steps you take. Just insert some space and move the intersection data after your x, y, and r input, then move the remaining intermediate steps to after that. Also, I just thought of it, if you do have erroneous data, you can multiple the affected values by L-TEST so that your results are zeros and not invalids.

twistedmosaic · 01-21-2008, 12:49 PM

Thanks Augi!

I'm making an excel spreadsheet to analyze (and eventually optimize) a specific type of 4-bar linkage. I always have one vertical link, and, for the purposes of the analysis, I step the bottom link in 5 degree incriments.
What I'm doing is using intersection of two circles to enforce compatibility on the floating link and the top rotor, by 'drawing' a circle with a radius the length of the floating link at the end of my bottom rotor, and 'drawing' a circle the length of my top rotor at the top of my stationary link....thus finding the point the links interesect. That's about all I can say without an NDA, but if you're more curious, I can PM you more detail about the specific application as thanks for your help

Hain · 01-21-2008, 01:03 PM

OH Christ this sounds like what we did in lab for 3 hours today. Sit in front of a screen and model 4-bar linkages... because they are so fucking thrilling!

Let me guess: 4-Bar, Working Model... and... Solid Edge? If this is cool you are using a vectorizing program on scanned blue prints. This analysis can be made easier if you use MATLAB (assuming you have it available). It can read and write to Excel documents.

Wait wait wait... An NDA... Non-Disclosure Agreement? {Yeah I am a quick one to catch on.}

twistedmosaic · 01-21-2008, 01:18 PM

Yeah, this isn't for school, it's for work, hehe. 4-bar linkages actually do have real world applications. I'll send you a PM with the details, and maybe you'll have a better approach than my brute-force excel method :-D

Hain · 01-21-2008, 03:19 PM

Crazy stuff there. I can't find the links to the 4-Bar program, nor to the MIT Open Course Ware with the MATLAB code that supposedly analyzes the same thing. Maybe someone else will peek in here and know what we are talking about.

01-21-2008, 01:53 AM	#1 (permalink)
twistedmosaic Inane Join Date: Jan 2004 Location: Wunderland	Math help: intersection of two circles? So, as a part of a larger excel 'program' I'm writing, I need a formula that will let Excel find the intersection of two circles when given their radii and the x and y coordinates of the center. It does not have to check for an intersection--in the range I'm using it for, they will always intersect. Also, they will always intersect at two points. Any ideas? The best sources I've found on the web are: http://local.wasp.uwa.edu.au/~pbourke/geometry/2circle/ and http://www.sonoma.edu/users/w/wilson...s/default.html but neither of them seem to lend themselves handily to easy translation into a single cell (or small set of cells) in Excel. Ideally I want this to be something nice and concise, so that I can automate finding these intersections with large sets of data, as a part of an optimization exercise. TIA, any help is appreciated __________________ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻

01-21-2008, 04:03 AM	#2 (permalink)
allaboutmusic Aurally Fixated Join Date: Oct 2007	I'm not going to be much help - I'd have done it the same way described. I would find the distance between the two intersection points, get the area of the two sectors (defined by the radii of the two circles and the arcs between the two intersection points), and subtract the area of the two triangles (defined by the radii of the two circles and the chord between the two intersection points). I have a feeling some optimisation can be done based on finding the length of the chord, and using basic trigonometry to simplify the formula to find the area. Last edited by allaboutmusic; 01-21-2008 at 04:06 AM.

01-21-2008, 12:49 PM	#4 (permalink)
twistedmosaic Inane Join Date: Jan 2004 Location: Wunderland	Thanks Augi! I'm making an excel spreadsheet to analyze (and eventually optimize) a specific type of 4-bar linkage. I always have one vertical link, and, for the purposes of the analysis, I step the bottom link in 5 degree incriments. What I'm doing is using intersection of two circles to enforce compatibility on the floating link and the top rotor, by 'drawing' a circle with a radius the length of the floating link at the end of my bottom rotor, and 'drawing' a circle the length of my top rotor at the top of my stationary link....thus finding the point the links interesect. That's about all I can say without an NDA, but if you're more curious, I can PM you more detail about the specific application as thanks for your help __________________ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻

01-21-2008, 01:03 PM	#5 (permalink)
Hain Pretty far out, man! Join Date: Jan 2005 Location: across the Atlantic pond	OH Christ this sounds like what we did in lab for 3 hours today. Sit in front of a screen and model 4-bar linkages... because they are so fucking thrilling! Let me guess: 4-Bar, Working Model... and... Solid Edge? If this is cool you are using a vectorizing program on scanned blue prints. This analysis can be made easier if you use MATLAB (assuming you have it available). It can read and write to Excel documents. Wait wait wait... An NDA... Non-Disclosure Agreement? {Yeah I am a quick one to catch on.} Last edited by Hain; 01-21-2008 at 01:09 PM. Reason: Automerged Doublepost

01-21-2008, 01:18 PM	#6 (permalink)
twistedmosaic Inane Join Date: Jan 2004 Location: Wunderland	Yeah, this isn't for school, it's for work, hehe. 4-bar linkages actually do have real world applications. I'll send you a PM with the details, and maybe you'll have a better approach than my brute-force excel method :-D __________________ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻ ☻☻☻☻☻☻☻☻☻☻

01-21-2008, 03:19 PM	#7 (permalink)
Hain Pretty far out, man! Join Date: Jan 2005 Location: across the Atlantic pond	Crazy stuff there. I can't find the links to the 4-Bar program, nor to the MIT Open Course Ware with the MATLAB code that supposedly analyzes the same thing. Maybe someone else will peek in here and know what we are talking about.