Using solver on excel

The solver function in excel is very powerful mathematical tool to do calculations under certain conditions. It works on objective cell by reforming the variable cells using sum constraints. The function is by default hidden in the Options settings so you need to  enable it. If you are wondering using solver on excel, this is the guide for you.

An example has been specifically designed to let you grab the idea and will answer your question regarding using solver on excel. The example illustrates we have a shopping voucher of sum Rs. 10,000 and we wish to utilize all of the money to buy specific stuff like Iron, kettle, watch, mobile etc. The problem is that we want to find out how we can purchase the stuff and utlize the voucher completely. The quantity, pricing and total pricing is mentioned.

Before starting the work, make sure to write the voucher amount somewhere for cell referencing in future.

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” is used to specify which value is changed to affect the Objective cell. Here we will specify Qty column because we want 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. We will tell the first condition that total amount and voucher amount should be equal. To do this, we will 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. To input the 2nd condition, again click on Add button. The 2nd condition is that we want the qty to be the whole value. So we 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 automatically converts to Integer.

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

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

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

19. Hit Solve button. The Solver has carried out operation. The conclusion is that in the given prices of different items, you can only purchase two mobiles if you want to fully utilize the amount fo 10,000. A dialogue box will also appear asking if you want to keep Solver solution or restore to original values. Click Keep Solver Solution and hit Ok

This is how using solver on excel can solve problems under certain conditions. 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.