Solving Complex Non Linear and Linear Equations by Excel Goal Seek: A step by Step Guide with Examples

 

1. Background

If you are interested in understanding the fundmentals of Excel Goal Seek feature and learning how to use it, you can access comprehensive information along with practical examples Here

Learn how to efficiently solve nonlinear and linear equations using Excel Goal Seek feature. In this comprehensive blog, we'll explore how Excel Goal Seek can simplify the process of finding solutions for complex non-linear mathematical equations, providing you with a convenient and time-saving approach.

Nonlinear equations often require numerical methods to find solutions. Fortunately, Excel provides a powerful tool called Goal Seek, which allows us to solve these equations efficiently. In this blog post, we will demonstrate how to use Excel's Goal Seek feature to find solutions for complex non-linear equation. We will start with simple linear equation then simple Quadratic and at last come the real deal of solving complex non-linear equation. Following are the three equation that we will be solving using Excel Goal Seek Feature:

a.    120X + 17 = 350

b.    (x + 34)^2 = 90

c.    (0.005 – X/20) * Cos(10X) + (1/200) * Sin(10X) = 0.03.

2      Using Excel's Goal Seek Feature:

Let's walk through the steps to solve these equations using Excel's Goal Seek feature:

1      

2      

    2.1     Set up the Excel spreadsheet.

a.    In column A, label cell A3 as “Variable-X".

b.    In column B, Cell B3 write the Equation.

    2.2     Solve the first equation: 120X + 17 = 350.

a.    In cell A4, enter "X" as the input variable.

b.    In cell B4, enter the equation: =120*A2+17-350.

    2.3     Use Goal Seek for the first equation.

a.    Click on the "Data" tab in the Excel ribbon.

b.    In the "Data Tools" group, click on "What-If Analysis".

c.    Select "Goal Seek" from the drop-down menu.

d.    Set "Set Cell" as B6 (the equation cell).

e.    Set "To value" as 350 (the desired result of the equation).

f.     Set "By changing cell" as A6 (the input variable).

g.    Data and filled Goal Seek Dialog box are shown below

 

h.    Click "OK" to run the Goal Seek analysis.

After running the Goal Seek function the result are shown below:

 

Download Excel File for Example-1

    2.4     Solving the second equation: (x + 34)^2 = 90.

a.    In cell A3, enter "x" as the input variable.

b.    In cell B3, enter the equation: =(x + 34)^2 = 90

c.     Point to Remember: This is a Quadratic Equation, therefore, the equation solution must have 02 roots

d.    Initially, we can make a reasonable assumption that the X-value for the equation is likely to be negative, considering the nature of the equation. However, even if you're unsure, following this procedure will still yield accurate results.

e.    Starting with variable -X as 0 we get the first root of -24.5. as shown below:

f.     Now to get the second root use following trick

                                           i.        Try inputting a value for "x" significantly lower than the first root. If the equation still yields the first root as the solution, proceed to the next step

                     ii.      Enter a value for the variable "x" that is considerably greater than the first root.

The figure above demonstrates this approach, using values of +50 and -50 in relation to the first root of -24.5.                                 

Download Excel File for Example-2

 

2.5     Solving Non-Linear Complex Equation: (0.005 – X/20) * Cos(10X) + (1/200) * Sin(10X) = 0.03.

a.    We start with a graph for X=-1 to X=1 with a step size of 0.1. the Graph is shown below

 

b.    From observing the graph, it is apparent that for X={-1,1} there exist two solutions where the equation's output is 0.03. One solution corresponds to a positive value of X between 0.75 and 1.0, while the other solution corresponds to a negative value of X between -0.5 and -0.75.

c.    By leveraging our mathematical knowledge, we can deduce the characteristics of the function without plotting the graph. Given that it contains cosine and sine terms, we can infer that it exhibits wave-like behavior or may produce multiple values. In our scenario, we need to determine the X value that yields an output of 0.03 

d.    Here we start Goal Seek with Variable-X =0 and apply Goal Seek , This process yields the initial solution of X = 0.864 resulting in an output of 0.03. we can also verify this by referencing the graph.

 

e.    As previously discussed, the other solution lies between X=-0.5 to -0.75.

f.        To compute this, we initiate Goal Seek using an initial X value of -0.5 and proceed with the goal seek process. This involves changing cell value in the Goal Seek dialog box to be -0.5. Consequently, we obtain a result of -0.564, meaning that for X = -0.564, the function's output is 0.03, which aligns with our desired result

                                             Download Excel File for Example-3

2.6     Interpret the results.

a.    Goal Seek provided the values of X that satisfy the respective equations.

b.    Review the results and consider any limitations or constraints specific to your problem.

3      Summary:

 Excel's Goal Seek feature offers a practical solution for solving nonlinear equations. By following the step-by-step instructions outlined in this blog post, you can efficiently find solutions to equations such as 120X + 17 = 350, (x + 34)^2 = 90, and (0.005 - X) * Cos(10X) + (1/200) * Sin(10X) = 0.03. Leverage Excel's computational capabilities to tackle nonlinear problems and gain valuable insights in various domains. Remember to choose appropriate initial values and evaluate the results within the context of your specific application.