How to use the solver in excel

The solver function in excel performs calculations under defined conditions. It works on objective cell by reforming the variable cells using sum constraints. You need to enable the function as it is hidden by default. If you are wondering how to use the solver in excel, this is the guide for you.

This example is specifically produced so you know how to use the solver in excel. The example illustrates a shopping voucher of sum Rs. 10,000 and we wish to utilize all of the money. We want to find out how to purchase the stuff and finally utlize voucher.

Note: There is not shortcut to access the solver function. You will always need to click the function.

Using solver to find solution

1. Go to Data tab and click. It will show options under this category

2. Now click on drop down arrow in What-if Analysis option.

3. Now click on Solver.

4. The Solver dialogue box appears.

5. The Set Objective field is where we want the value to change from 9,300 to 10,000. Click on the arrow next to the field

6. Now click E9 cell.

7. Press Enter. The value is displayed under the field.

8. The “By Changing Variable Cells” specifies which value is altered to impact Objective cell. Specify Qty column to change this qty so the amount comes upto 10,000. Click the arrow next to the field.

9. Now bring mouse at C4, click and hold at C4 and drag vertically to C8. This will select the values under Qty column.

10. Press Enter, the values are specified in the field.

11. To set certain criteria for getting the result, we will use Subject To The Constraints filed. Click Add button.

12. A dialogue box appears with 3 conditions.

13. Now the first condition is total and voucher amounts be equal. Input E9 in Cell reference, put condition as equal and input D12 as Constraint. The dialogue box will look as below.

14. Now Hit enter, the first condition is selected.

15. click on Add button to input the 2nd condition. We want the qty as whole value. Click arrow next to Cell reference, bring cursor to C4, click and drag upto C8 and hit enter, then input “int” in the condition, the Constraint converts to Integer.

16. Hit Enter. The 2nd condition is also assigned.

17. The last condition is to have a qty equal to or greater than zero. For this, again click on Add button. Select Cell range from C4 to C8, add in Cell Reference, assign equal or greater than symbol in middle section and finally in Constraint field, input zero (0).

18. Hit Enter, we have assigned the 3rd condition also. Now all three conditions are specified and the dialogue box looks like this.

19. Click the Solve button. The conclusion is you can only purchase 2 mobiles under given circumstances and voucher amount. A dialogue box appears asking to keep Solver solution or restore original values. Click on Keep Solver Solution and press Ok.

This is how to use the solver in excel. Need to edit Word/Excel/PPT file free of charge? Download WPS Office edit files like without any cost. Download now! to get enjoyable working experience.