### Microsoft Excel: Goal Seek Technique for solving mechanical engineering design's problems

Problems in mechanical design often require the solution of nonlinear equation such as

3x

^{3}- 20x^{2}+ 1000x + 12000 = 0
These problems are sometimes stated in the form, find the zeros or roots of the function

f(x) = 3x

^{3}- 20x^{2}+ 1000x + 12000 = 0
that is, find the values of x that make f(x) = 0. Therefore, they are sometimes called

**root solving problems**. The equation shown above is a typical equation encountered in the problem of finding the maximum stress at a point. While such equations can be solved by trial and error (simply guessing value until the left-hand side of the equation is nearly zero), there exist simple, systematic ways to solve such problems. Two such methods - the Method of False Position and Newton-Raphson method.
The

*is a systematic method of narrowing down the region in which the root exists. And another one is the***Method of False Position***of root solving. It is more sophisticate than the Method False Position. It uses the slope of the function to estimate the location of the root. For most functions, this method converges much more quickly to the solution than does the Method of False Position, and the Newton-Raphson method is the method of choice in most cases (it is my favorite root finding technique, I would like to share you the Excel VBA of the Newton-Raphson method later). However, there are certain types of functions that are not solved very well by the Newton-Raphson method, so the Method of False Position is a good method to use on these functions. The Newton-Raphson method uses the tangent line to the curve at the point x***Newton-Raphson method**_{n}to estimate the location of the root. The slope of the tangent line at x_{n}is just the derivative of the function evaluated at x_{n}. More info about these root solving techniques will be shared later.
In this post, I would like to share a very simple Excel technique to you in order to help you solve your problem easily on your desktop PC. It is called "

**Microsoft Excel Goal Seek**" technique. Knowledge of Microsoft Excel VBA programming is not required for this technique.**Microsoft Excel: Goal Seek Technique for solving mechanical engineering design's problems**

**1. Open Microsoft Excel and enter the value and formula as shown below (Click at the picture for larger view)**

**2. After that, select Tools > Goal Seek...**

**3. Enter the values as shown below. This is to let Microsoft Excel change x [cell E14] until the value of f(x) [cell E16] is zero (or nearly). Then click OK.**

4. You will see the following screen if the solution is found. Please note that the solution of x that gives f(x) = 0 also depends on the initial value of x you entered. Sometimes Microsoft Excel Goal Seek tells you that there is no solution found, but when you change the x value (=value in cell E14 for this case) to any other value instead of your first initial value, Microsoft Excel Goal Seek may find the solution.

Remark: The current value [f(x)] is not zero because of iteration process, but there is no problem if it is within your tolerances.

What if you want to design your shaft that has

**fatigue loading**using**the maximum-shear-stress theory combined with the Soderberg line for fatigue**, which the diameter and**safety factor**are related by the following equation?_{m}that can apply to your shaft with known diameter (d) and other design factors shown in the equation, what you need to do is to set the several cells for each input factor e.g. d, n, M

_{m}, M

_{a}, T

_{a}, etc. in Microsoft Excel. Next step is to set the formulas for both of your left-hand side and right-hand side of the equation.

So your LHS is something like =PI()*E20^3/32 (suppose that the value of d is in cell E20), then do in the same way for your RHS. Next, enter formula in another cell to calculate the difference between LHS and RHS. I suppose that you enter this formula in cell F11

Then use Microsoft Excel Goal Seek to find value of M

_{m}that make difference between LHS and RHS equal zero.
So values in Goal Seek dialogue box will be...

Set cell: F11

To value: 0
By changing cell: E12 (suppose this the value of M

_{m})
Imagine that if you want to fix the value of M

_{m}and find value of d instead. What you have to do is just to change the value in "By changing cell:" to the cell that stores the value of d. So it gives more flexibility to you.
Another technique is

**Microsoft Excel Solver**. It is more sophisticate and very useful tools that can help you solve optimization problem. But it requires more customization, we will talk about this later.
Hope this is useful for you :)

password: mechanical-design-handbook.blogspot.com

**DOWNLOAD EXAMPLE OF MICROSOFT EXCEL GOAL SEEK FOR FREE**

## Comments

I need to generalize this equation solver thing for some calculations, so that, i can get the solution every time on changing value of coefficient.

for example;

in a cubic equation, if i change coefficient a,b,c,d then also, i can get the solution.

Please help.

Thanks !!