This post is intended to give you an example of using Solver in Excel to solve a causal problem in daily life – buying postal stamps. Don’t consider it a tutorial. 😛
Have you ever heard of Solver in Excel?
I think Solver is not a common tool to most users, regardless of years of experience of using Excel. I learned Solver in my MBA study a decade ago. I was impressed by the power of Solver. Nevertheless, I have never encountered a situation that I needed Solver in my work life, so far (probably due to my job nature). Solver is just for fun to me until one day I went to post office…
There was a super long queue and I was not patient enough to wait. Therefore I went to weigh my small parcels and see how much fare was required for each parcel. It was HK$9.8 and HK$11.7. Then I went to the self-service stamp vending machine to buy the stamps… Wait, there were only four options available. I don’t remember the four options, let say HKD1.7, HKD2.2, HKD3.0, HKD3.7.
Obviously, the question was: How many stamps of what values should I buy, so as to minimize the total amount to be paid while ensuring enough postal fare???
Standing in front of the machine, it took me quite a while to figure out the solution.
After I got home, I decided to solve the problem in Excel! First, I think of formula. To be honest, it would be far more complicating… i.e. I don’t know how. 😛
Then Solver came to my mind! See the demonstration below:
Isn’t it cool?
You may download the Sample File – Solver usage in daily life.
(Somehow WordPress.com does not support .xlsm, therefore the sample file is a marco-free file. Instead of clicking the textbox to run the solver, please go to Data Tab –> Analysis Group –> *Solver –> Solve; note: do not change the parameters set)
I am not going to give you a detailed explanation on how it works, but you may see the <Brief Explanation> in the file.
While using Solver in Excel is not difficult, understanding the problem and building a corresponding model for Solver is. Not to mention the different Solving Methods: GRG Nonlinear, Simplex LP, Evolutionary. 😛
The model is not perfect. In some circumstances it refuses to give optimal solution (local vs. global) where there is an obvious one. Honestly, I have not master the skill of using Solver yet. As mentioned in the beginning, I hope to show you an example of using Solver – a powerful tool in Excel and hence raise the awareness of Solver.
If you have suggestions to enhance my model, please leave your comments.
*Note: If you do not see “Solver” on your Data tab, follow the steps below to install the Solver Add-in.
File –> Options
Check the Solver Add-in, then press OK, and OK
You should see the “Solver” on Data tab
Hi, I have an never finishing project with solver and loops. There is a cutting list with dimensions and quantities. There’s also a list of available entities to cut from. To start we take a single dimension:length. Exemple a rebar cut into steel rods. This is quite similar to what you posted, except for the Loop, most of the times we need more than just one rebar. Might be good for a solver post. Thanks for the blog.