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 = x3-6x2+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.