Section L2.2: Iterative Solution of Single Algebraic Equations

The method of using the spreadsheet model generator for sets of equations suitable for direct, non iterative solution has already been described in section L2.1

The model generator does not itself provide a mechanism for solving equations iteratively. However the facility for solving single equations using a `goal seek' facility is available in nearly all spreadsheet systems.

We therefore use the model generate to provide evaluation of the function or left hand side of the equation which it is required to reduce to zero. An illustration for one of the exercise examples is shown below.

model single

variable x, fx
end variable

equation
  fx = x + @ln(x)
end equation

steadystate

end model

Notice that there is only one equation here, but there are two quantities declared as variables, x and fx. The latter is not really an unknown, it is the function value which is to be reduced to zero. using `goal seek'. (Note in passing the form of a function call in the model generator; the function name must be preceded by a`@' whether or not the spreadsheet requires that convention. Also that Excel appears to interpret LOG as base 10, although the JavaScript solver referred to elsewhere, most other programming languages and mathematical convention treats this as the natural log!)

Alternatively, if the spreadsheet does not have a search facility, it is possible, for single equations, to use the logic of a bisection search by hand.

The above model program can be copied from here. Copy it and try both an automatic solution (if available) and a hand directed trial and error.

The model generator is here.

A .slk format translation is already available here. You can copy it and load it into Excel. Cell C5 contains the unknown x (defaulted to zero) and cell D5 contains the function value fx. Initially D5 displays an error message because an attempt has been made to calculate the log of zero.

Change C5 and observe changes in D5. Note that values of 0.1 and 1.0 bracket the solution. Try choosing values by hand to `home in' on the solution when fx in D5 will be nearly zero.

To use the `Goal seek' feature of Excel, proceed as follows:

Click on D5, pull down the `Tools' menu and then `Goal Seek...' submenu.
A box will come up with 3 slots..
- Set cell: Which should have D5 in it; if not click on D5
- To value: Enter 0
- By changing cell: Enter or click on C5
Click OK and the solver should find a value for x around 0.567

This may seem a rather cumbersome procedure for dealing with single equations, but it becomes useful where we have a set of equations which can be solved by iteration in a single variable, see section 3.5.1

Return to Modelling Lab

Course Organiser

Last Modified 2/9/00