CHE 317: COMPUTER PACKAGES FOR
CHEMICAL ENGINEERS
(INTRODUCTION TO THE USE OF EXCEL)
LECTURE III
Instructors:
Engr. (Dr) Efeovbokhan, V.E and Mr. A. T. Ogunbiyi
Solving Equations in Excel Using Goal Seek
Goal Seek permits rapid solutions of algebraic equations using
iterative (i.e trial-and-error) techniques, based upon a series of
successive approximations derived from an initial guess.
The accuracy of each successive value is refined until the values
converge to a stable solution.
In Goalseek, it is necessary to define a cell for the variable you
want to change (X) and a cell for the function you want to
evaluate.
Note:
Goal Seek will only work if you tell it to evaluate some function
until it reaches a constant value.
You cannot tell it to equal something that can change, so for
example you cannot do something like this: X - X3 = 2X2 + 1
because neither side is a constant.
The easiest way around this is generally to solve the function for
zero and then use that as the evaluating function.
The proximity of the initial guess to the desired root
increases the likelihood of obtaining a converged
solution.
Goal Seek is very useful for quickly solving any
formula for a single unknown value
A warning message will be generated if the
computation does not converge or if an
inappropriate root (e.g. a complex root) is about to
be generated.
Example: Solve X3 +2X2 -X+1=0
Step 1: Open a spreadsheet and enter the following
formula in cell B1.
= A1^3 + 2*A1^2 A1 +1
Cell B1 is the equation that should be zero; and cell
A1 contains the variable that is repeated changed to
make this happen. Enter 2.0 into cell A1
Step 2: Under Tools, choose Goal Seek. Or
Under data, click on What if analysis and choose
Goal Seek. When the dialog box appears, enter the
following as shown below:
Step 3: Click OK. The answer appears in the
spreadsheet
Example: Solve X3 +2X2 -X+1=0
The answers appears in the spreadsheet
- 2.54686 - 0.00035
Hence, the solution found is
- 2.54686 with a tiny error.
The test of whether the calculation is correct is shown
in cell B1 which is 3.5E-4, and very close to zero.
Solve sin(x) + x = 5 using Goal Seek
If your guess is right and the result was correct,
then the solution has been found.
If not, you could repeat by using a another guessed
value
If it cannot find a solution its because the guess is
not sufficiently close or the equation is not solvable
SOLVER
A more powerful version of Goal Seek.
Allows you to solve systems of equations, and
linear equations that you will come across from
mass and energy balances.
It operates under similar principles as Goal Seek.
And it can set constraints to the final solution
can restrict the solution to a range which makes
physical sense.
Solution of Solve X3 +2X2 -X+1=0 using
Excels Solver
You can solve the same problem using solver option in excel
Step 1: Enter the given equation in cell D3.
= C3^3 + 2*C3^2 C3 +1
Cell D3 is the equation that should be zero; and cell C3
contains the variable that is repeated changed to make this
happen. Enter 2.5 into cell A1 as the initial guess
Step 2: Under the Tools menu, click on Solver. If the
choice solver does not show, choose Add-Ins and load
Solver from the Analysis ToolPak or the original Excel
program disk, OR go to the data tab and click on solver
at the far extreme right.
Solving X3 +2X2 -X+1=0 using Excels Solver
Step 1 displayed
Solution of Solve X3 +2X2 -X+1=0 using
Excels Solver
Step 2 displayed
Solution of Solve X3 +2X2 -X+1=0 using
Excels Solver
Results:
Cell A1 and B1 display:
-2.54682
4.06E-07
This time, it is much more accurate as the error
is extremely small.
Exercises
Class Work
1. Solve the equation sin x + x = 5 using Solver
Practice Exercises
1. Determine a real root for the equation
2. Determine the smallest positive real root for the
equation
x + cos x = 1 + sin x
Solution to class work
Solve sin(x) + x = 5
Enter this formula into cell
D3
SOLVER for one unknown: sin(x) + x = 5
Initial guess
Click on solve
Solution to sin(x) + x = 5
Desired root
Negligible error
Announcement
Assignment 2 is due for submission on Friday 11:55 pm
25/09/2015
Excel lecture slides 1 and 2 have been uploaded on the
Moodle platform.
Your assignment 3 will be uploaded on the MOODLE 2
Platform after the Friday class.
Always check the electronic learning platform for
information about the course
Go to www.moodle2.covenantuniversity.edu.ng
Enrol as a student under the course
Use CHE317 as your enrolment key
Adhere strictly to the submission deadline
