Using AutoCAD and Excel to Iterate to a Solution

SHARE
Share on email
Share on linkedin
Share on facebook
Share on whatsapp

When we measure geometry for reverse engineering it’s not always possible to get to a solution using easy classical drafting techniques.

In some instances, AutoCAD can be used to give the solution to complex geometry down to about the eighth decimal place. After modelling a design in 3D I usually pick up discrepancies in the eighth or seventh decimal place. As an example, Imagine you have to calculate the intersection of three spheres. The mathematics is quite tedious, yet AutoCAD can calculate the solution using solid geometry at the click of a few buttons.

In other instances, it’s not that easy.

Given:

  • An outside arc length of 3320mm.
  • An offset of this arc of 100mm to the inside.
  • The inner arc has a chord length of 3080mm.

Question:

  • “What is the radius?”

Micrographics, AutoCAD, Excel, Iterate Solution, Curve Fit

My first step is to draw a sketch to aid my understanding. If I can’t figure out the solution, then I start with a guess, draw the geometry and record the data point In Excel.

Micrographics, AutoCAD, Excel, Iterate Solution, Curve Fit

Then I take another guess and repeat the process. Is the value closer to the solution I want or not? If I overshot then I move towards the other direction by half the interval. This is based on the Bisection Method and it works well on simple functions if the correct interval size is used|:

After a while, it became clear that the solution followed a trend.

Micrographics, AutoCAD, Excel, Iterate Solution, Curve Fit

Now Goal Seek is a great tool that quickly finds the value one needs.

Micrographics, AutoCAD, Excel, Iterate Solution, Curve Fit

I set up the trend line equation and then specify that I need the radius that will give a difference in Arc Length of zero.

Micrographics, AutoCAD, Excel, Iterate Solution, Curve Fit

Now Excel does not always give the greatest mathematical solutions to curve fitting, my own preference is to use MATLAB. In this case, the solution is clearly off.

Micrographics, AutoCAD, Excel, Iterate Solution, Curve Fit

In its absence, since the solution can be inferred from approximating the curve as a straight line close to the root (Δ Arc Length = 0). The Chart is rearranged to show a smaller portion of the solution and the solution is more closely guessed.

Micrographics, AutoCAD, Excel, Iterate Solution, Curve Fit

After iterating I quickly got the radius to a whole number while the curve length was off by 0.03 mm. For the application it was meant for this was more than sufficient accuracy, but if needed the solution may be iterated to a much greater accuracy.

Micrographics, AutoCAD, Excel, Iterate Solution, Curve Fit

This type of method can give solutions in some situations faster than I can do the math. Perhaps your math is better than mine? Whatever the case, contact us if you would like to learn this method or if you need to contact us to solve such problems for you.

 

RECENT ARTICLES