An example of using Solver for a causal problem in daily life

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???

Excel Tip - Solver 1.PNG

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:Excel Tip - Solver 2.PNG

Isn’t it cool?

You may download the Sample File – Solver usage in daily life.

(Somehow 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 –> OptionsExcel Tip - Solver 3

Check the Solver Add-in, then press OK, and OK

Excel Tip - Solver 4

You should see the “Solver” on Data tabExcel Tip - Solver 5.PNG


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in General and tagged , . Bookmark the permalink.

1 Response to An example of using Solver for a causal problem in daily life

  1. jan Martens says:

    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.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.