Tricky Math Problem 2

[BlaineW]

Ok, looking for a little help with an analysis I'm doing for work. I can iterate to solve individually but my challenge here is that I'm trying to do it for a large population of random points that have been generated as part of a tolerance sensitivity analysis done in excel on a linkage mechanism.

Per the drawing attached I have coordinates relative to Origin 0 for points B and D and based on that everything else is known. What I would like to do is determine the resultant angle phi if I rotate O-B about point A such that the two arcs become tangent. I know that (Cx-Ex)^2+(Cy-Ey)^2=(2*r)^2 for tangency and I know my B1x^2+B1y^2=B2x^2+B2y^2. But is it possible to have excel solve? Tried using circular reference and turning on iteration but didn't appear to function like I was hoping.

I'm expecting I need something like a matlab to do this but I would like to do it for a large set of points so I can determine probability of manufacturing rejects based on that deflection. I'm hoping there is something obvious I'm missing, but my head is spinning at this point.

Thanks for any help.

 

[TheTick]

Excel has a goal seeking algorithm that can iteratively solve. Look up "goal seek" in the Excel help.

Alternatively, you could have a CAD jockey sketch the solution and get the point coordinates.

 

[rb1957]

as i understand it ...
one arc is (x-Ex)^2+(y-Ey)^2 = rE^2
the other is (x-Cx)^2+(y-Cy)^2 = rC^2
and (Cx-Ax)^2+(Cy-Ay)^2 = rA^2 (OB is rotating about A, C is fixed to OB)

but you know that C-E is the sum of the radii, rC + rE; the tangency is on a line joining the centers ...

i think i'd draw it in autocad

Quando Omni Flunkus Moritati

 

[Occupant]

Before I do anything with this, is this what you want?

 

[chicopee]

To put it bluntly, the OP's sketch and mathematical equations are worthless because it does not show me how elements D and E rotate, if C and E are fixed to B and D, what rC,rE,etc... represent. So if a proper answer is to be given you should reintroduced your question so that the sketch and the equations are understood under the principles learned in school.

 

[Denial]

I haven't looked at your diagram or thought about your equations, and am attempting to give a general answer to your question "is it possible to have excel solve".

Excel's "Solver" add-in can be used to solve non-linear simultaneous equations.[ ] Here's an approach for the case with three unknowns, readily extendable to larger or smaller problems.
» Convert your equations to the form
[ ][ ][ ]F(x,y,z) = 0
[ ][ ][ ]G(x,y,z) = 0
[ ][ ][ ]H(x,y,z) = 0
» Guesstimate a set of starting values for x, y & z.[ ] Place each of these guesstimates in a separate cell.[ ] (How close these need to be depends upon how non-linear your problem is. The closer the better.)
» Based on these starting values calculate the values of F, G & H in a separate cell for each.[ ] If you guesstimated correctly (which of course you didn't) the three cells should each be exactly zero.
» In a fourth cell calculate an "objective function" F²+G²+H².
» Fire up the Solver, and ask it to minimise the objective function by varying the (x,y,z) values.
» Bingo!

 

[prex]

If I understand correctly your problem and if your sketch represents more or less to scale the relative positions, then C will displace more or less perpendicularly to the horizontal of your sketch. In this case you can have an approximation with this procedure:
-calculate the actual distance CE
-determine the change of this distance by the rotation: Δ=2r-CE
-the angle is Δ/CA
Otherwise (as in Occupant's sketch) you need to go through determining the initial and final angles of CE and CA.

prex
[URL unfurl="true"]http://www.xcalcs.com[/url] : Online engineering calculations
[URL unfurl="true"]http://www.megamag.it[/url] : Magnetic brakes and launchers for fun rides
[URL unfurl="true"]http://www.levitans.com[/url] : Air bearing pads

 

[MITProfessor]

chicopee,

It can show how D and E rotate using CAD system.

 

[BlaineW]

Yes Occupant that is what I'm looking for.

As I mentioned finding an individual solution is fairly easy, my challenge is that I have a large set of points I'm looking for solutions to use for statistical purposes.

Denial- I'll look into the solver, I'll have to get IT to install it for me as they seem wary of anyone installing anything on their own.

Thanks everyone.

 

[rb1957]

C rotates around A, yes?
E rotates around "F", the other end of the line through D, yes?
for various angles phi, you want to calc the co-ord of E ?

you say "everything else is known" ... does that mean lengths OA (not that it matters), AB, BC, DF, DE, radius C, radius E ?
the co-ords of A and F ?

maybe solve as three known lengths AC,CE,EF = AF, the two variables are phi and the angle between AC and CE
now you have only two equations (x- and y- dim'ns) amd two unknowns.

Quando Omni Flunkus Moritati

 

[JStephen]

If I'm interpreting the problem right-
Make a triangle from A to B to D and back to A.
Presuming you know distance A-B.
Distance B to D at the point of tangency is the sum of the two radii.
Distance A to D is taken from the pythagorean theorem, assuming you know where Point D is relative to Point A.
You then know three sides and none of the angles of that triangle.
Using the Law of Cosines solves directly for Angle B-A-D.
The angle from horizontal down to D is the inverse tangent of (yA-YD)/(xD-XA)
And angle phi is the difference between those two angles.

 

[IDS]

If the problem can be set up for solution using the Excel goal-seek function, then the link below has a macro that will automate the goal-seek process for any number of different cases, rather than the laborious process of entering each case by hand:

http://newtonexcelbach.wordpress.com/2009/07/25/using-goal-seek-on-multiple-cells/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[prex]

This is a step forward with respect to te (crude) approximation in my previous post.
Calculate:
d=length of AC
δ=2r-length of CE (this is the elongation of CE)
sin β=sine of the angle of AC to CE (this is simple geometry: example)
Now the displacement of C is Δ=δ/sin β and the angle sought φ=Δ/d
This is still an approximation: it doesn't require anymore, like my previous one, β to be close to 90 deg, but still requires φ to be small (a few degrees).


prex
[URL unfurl="true"]http://www.xcalcs.com[/url] : Online engineering calculations
[URL unfurl="true"]http://www.megamag.it[/url] : Magnetic brakes and launchers for fun rides
[URL unfurl="true"]http://www.levitans.com[/url] : Air bearing pads

 

[JStephen]

Sketch attached.
I'm assuming you know or can find X and Y coordinates for A and D and know the radius of both circles and the distance from A to B.
In that case, you know distance A-B and find distance A-D from the Pythagorean theorem.
Distance B to D is the sum of the two radii.
You then know 3 sides of the triangle A-B-D and find the angle B-A-D directly from the law of cosines, no iteration involved.
You find angle gamma from inverse tangent function, subtract that and angle B-A-D from 90 degrees to get phi.

Maybe I'm overlooking something that makes this harder and requires iteration, solvers, etc., but it looks pretty straightforward to me.

 

[Occupant]

I worked through one example - copy attached. Since this was done in Mathcad I used their Solve Block, but you don't have to do that by solving the equations algebraically.