# Using AutoCAD and Excel to Iterate to a Solution

SHARE

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: 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. 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. Now Goal Seek is a great tool that quickly finds the value one needs. 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. 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. 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. 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. 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.