Microsoft Excel Solver Tutorial
The Microsoft Excel Solver tool is an extremely useful way to take some more complex math work off yourself. You can use the tool to automatically solve a problem, such as the number of pizzas one needs to sell to make the most money. It can even handle certain constraints, such as the maximum number of pies you can make. The quickest way to learn how to use the Solver tool is by experimenting with the tool, and learning how it functions.
Other People Are Reading
Instructions
-
Example Setup
-
1
Open Microsoft Excel and start a new spreadsheet.
-
2
Input the first section for an item, for example, pizza. Type in "Price," move down a cell, then type in "Quantity", and then "Subtotal" below that.
-
-
3
Move down two cells, then make a second one. Repeat to make three different "pizzas." Name the three different flavors, such as "Cheese," "Pepperoni" and "Premium."
-
4
Set a price for the three pizzas: "10.50," "11.50" and "15", for example.
-
5
Write the subtotal formulas. The formula should be "=C2*C3", for example. Repeat for all three of the pizza types. The formula should cause an error because the number of pizzas is blank. This is because the Solver will fill those in.
-
6
Move over two columns and type in the "Total Revenue" column. Make this the sum of the three subtotals, more accurately "=C4+C8+C12" or whatever the cells are on your spreadsheet. Again, the formula won't appear because it hasn't been solved yet.
-
7
Make three total number sections. "Total Cheese," "Total with Meat" (i.e., pepperoni and premium) and "Total Pizzas." Make these the sums of their corresponding pizzas.
Run the Solver
-
8
Set up some constraints for the solver. For example, the maximum number of pizzas your shop can make is 500. You have enough pepperoni and meat for 300 pizzas, but only enough veggies for 150 of the premium pies. Make a box on the spreadsheet and write in these cells.
-
9
Open the Solver tool. It's located under the "Tools" menu, as "Solver...."
-
10
Select the Target Cell. This is the cell your solver will solve for. Click the "input" icon next to the cell, and select the "Total Revenue" cell.
-
11
Click the "Solve For:" radio button for the "Max" option. You can also select "Min" and "Value of:" if you need either of those options instead.
-
12
Input the "By changing cells" choices. Click the "Input" icon next to the field, and select the "number of" cells. You have told Solver it can change those numbers as needed for the problem.
-
13
Input the constraints in the box. Click the "Add" button and select the corresponding cells. So, for the meat constraint, select the "Total Meat" cell in the "Cell Reference:" window, and the "Max meat" cell under "Constraint." Select the operation; for this you want "<=" or "less than or equal to," then click "OK" when done. Repeat this process for the other two constraints.
-
14
Click the "Solve" button. Give the spreadsheet a few moments to solve the equation. You will see that it maximizes the profit, but within the contraints you input.
-
1
