telekinetic

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

__________________
twisted no more

allaboutmusic

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

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.

Attached Files

circle_intersection.zip (5.8 KB, 82 views)

__________________

////\\// IRONSKY.NET \\//\\\\

telekinetic

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

__________________
twisted no more

Hain

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.}

__________________

////\\// IRONSKY.NET \\//\\\\

telekinetic

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

__________________
twisted no more

Hain

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.

__________________

////\\// IRONSKY.NET \\//\\\\

Mainelion

I'm brand new here. I discovered the site while looking for a way to calulate the intersection area of two circles given their radii and distance apart.

I would like to get a copy of the circle intersection.zip spreadsheet in Hain's post, but when I download the file liked in HAin's post, I get a corrupt file error when I try to unzip it. Any help would be appreciated.

Thanks

Mainelion

Cynthetiq

you must be doing something wrong as I just downloaded it and it unzips just fine. try again, and if it fails send me a PM and an email address.

__________________
I don't care if you are black, white, purple, green, Chinese, Japanese, Korean, hippie, cop, bum, admin, user, English, Irish, French, Catholic, Protestant, Jewish, Buddhist, Muslim, indian, cowboy, tall, short, fat, skinny, emo, punk, mod, rocker, straight, gay, lesbian, jock, nerd, geek, Democrat, Republican, Libertarian, Independent, driver, pedestrian, or bicyclist, either you're an asshole or you're not.

telekinetic

Wow cool, blast from the past. Since I don't work there anymore, what I was doing was calculating 4-bar linkages for TV mounts to simulate 'zero gravity' for tilt. What I mean by that: I was trying to find a linkage combination that kept the center of gravity at as close to the same height for any tilt angle as possible. This cancels any effect gravity would have at undoing the desired tilt angle you just set your TV to. If I get a chance I'll dig up some diagrams to explain it better.

__________________
twisted no more

Mainelion

Thanks for the offer. I put together a sheet myself that seems to work OK. I tried downloading the file again and I still get the message "The compressed (zipped) folder is invalid or corrupted" when I try to unzip it.

It would be interesting to compare the two files. I sent a PM with my email if you get a chance to send it along.

Thanks

filtherton

Were you able to do it? I remember linkages from one of my ME classes. All I remember about them is writing a matlab code using complex exponential functions.