Solving Complex Non Linear and Linear Equations by Excel Goal Seek: A step by Step Guide with Examples
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:
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.
Comments
Post a Comment