**3. Using
Solver to Find Zeros, Minima and Maxima of Functions**

**1. General Advice and Background.**

In practical problems, we often need
to find the zeros, or minima, or maxima of a given function. For example, in break-even
analysis, we want to find the zero of the profit function. We also often need
to find the minima of the costs, or the maxima of the profits for various
activities. We have learned to use Excel to draw the graphs of many functions,
and the graphs can be used to find the zeros and minima and maxima. However,
this is not enough if we want sharp accuracy for the solutions. In this class,
we learn how to use **Solver** to
achieve this goal.

**2. Using
Solver to Find the Zeros of a Function.**

Suppose we have the function y = x^{3}-6x^{2}+11x-5.9
and we want to find the zeros of this function. It helps if we know this is a
cubic function and there are at most three zeros. But we don't have much idea
about where the zeros are. To use **Solver**
efficiently, we need first to have a reasonably good initial guess of the
solutions. To achieve this, let us check whether the range of x between -10 and
10 contains any zeros of the function. We can enter the x values -10, -9, ¡,
-1, 0, 1, ¡ , 9,10 into A2:A22 using **Autofill**, and then enter the formula = (A2)^3-6*(A2)^2 +
11*(A2)-5.9 into B2 and then use **Autofill** to find the values of the function in cells B2:B22.

Inspecting the
values in B2:B22, we observe that the zeros seem to lie in the x range from 0
to 4.

(Tips: Have
some difficulties in finding the zero points? You can use chart to facilitate
the observation. Go to Prac2: Drawing Graphs)

Now we change the x values in A2:A22,
to 0, 0.2, ¡ , 3.8, 4. Then the y values in B2:B22
change automatically to their new corresponding values. Now we see that there
should be 3 zeros, one near x = 1, one near x = 2 and one near x = 3. If you
like, draw a graph of the curve using the **Chart
Wizard.**

We are ready to use **Solver** now. We follow the three steps
listed below.

(a) Click *Tools ->Solver*

The Solver Parameters box now appears.

(b) At the box **Set Target Cell**, we enter B7, then click the **Value of** option and enter 0 and then at the **By Changing Cells box**, we enter A7. These steps mean we want to
achieve 0 in B7 by changing the value in A7.

(c) Now we
click the box Solve and find that the values in A7 and B7 become 0.953319833120002 and 8.34443628860981E-07 respectively.
This means if we take x = 0.953319833120002, then y = 8.34443628860981x10^{-7}.
Therefore, x = 0.953319833120002 is a very
good approximation of a zero of the function.

The **Solver Results** box now appears and we click the **Keep Solver Solution** option so that the
new values in A7 and B7 will be kept.

(d) Using **Solver** to Find the Maxima or Minima of a Function.

Checking the values in A2:A22 and
B2:B22 we see that in the x range from 0 to 3, there is a maximum value for y which
occurs near x = 1.4.

To use **Solver** to find the actual value of the maximum, we follow the 3
steps used in finding the zeros above, with the following changes:

In Step 2, we enter B9 in the **Set Target Cell** and click **Max** instead of **Value of**. In the box **By**** changing Cells**,
we enter A9.

At the end, we should have
1.4226497129766 and 0.48490017945975 in A9 and B9 respectively. This means the
maximum is y = 0.48490017945975 which occurs at x = 1.4226497129766.

This finishes the class.