Microsoft Excel has a lot of functions that can prove to be useful. There are some tools which can make your calculations a lot easier. However, you should also know that there is an option that will enable you to find optimal solutions. Here, you will find the step-by-step to use a solver to your benefit.

  1. Enable Solver Add-In

You have the solver add-in by default in Microsoft Excel. However, it is disabled till you enable it to use. You need to follow few steps to enable it. First, click on the file menu and click on the options tab. Hereafter, you will see an excel option box, click on the solver add-in which is located under the heading called add-in and ensure that it is highlighted with blue color. Click on go box in manage-excel add-in. after clicking on it, you will notice box. Check the add-in box check that is next to the solver under the add-ins and click on the okay button. The solver is enabled. Open Excel and click on the data tab. You will find the solver is located under analysis.

  1. Example Function

Solver has functions which enable you to find a solution to the root of the equation. A simple function y=3x^2 +3x-5 is something that you do by hand. However, for the solver, you will have to define the function properly. Assign the variable to the function in a single cell. Define the function in another cell by f(x) = in cell A2 and type 2*B1^3+3*B1-5 in B2 cell. Cell B1 acts as an X in the formula and thus, if you change the value in B1, the results will change.

  1. Under the Solver Parameter Box

When you click on the solver option, you will find a solver parameter box. Specify the parameters for running the solver. Set Target Cell Box contains the location of the cell for the problem’s objective function. When you choose the value, the solver will try to find the value of cell targeted to the value which is placed in the box. Make sure that the Cell box has the location of decision variables. Also, specify the Subjects to Constraints box just by clicking on the option. Change modifies the constraint you entered, delete deletes the entered constraint, and reset will clear the problem.

  1. Set Target Cell Along with Equal to

With solver, you can solve for formula in one cell. This is called a target cell. It represents the object. By setting the target cell, you will identify the position of the function that you want to solve. On the other hand, the equal option will enable you to identify the option that you plan to carry out. It will give the maximum or the minimum value. The value option will let you choose the value you want to be solved for.

  1. Set the Change Cells and Solve

Changing cell is actually variable cells since it identifies the cell which has the variable of the problem. Here, B1 is the cell that contains the value of X, proceeds to the box under only by changing cells and put in $B$1. Finally, just click on solve option.

Source:

151 views