Do you ever think about questions like this?
- What is the maximum profits we can make?
- What is the best way to schedule employees in shifts?
- What the best combination of tasks we can finish in a given time?
You might have heard about Excel Solver tool while trying to find solutions to questions above. If you have never used Solver or have little idea about it, then this post and video are for you.
What is Excel Solver really?
Excel Solver can solve problems for you. That simple!
For a given problem, excel solver can run various permutations and combinations and find out best possible solution for you. It is like goal seek, but better & awesomer.
Excel Solver Tutorial:
It is tricky to explain what solver does in text. So I made a short video (13 min). In this video you can learn,
- What is solver really?
- How to use it – a simple example.
- Finding the next month with 5 Fridays, 5 Saturdays and 5 Sundays using Solver
Watch the Excel Solver Tutorial video:
Few Tips while using Solver:
- Define your problem first: In order to make the best use of solver, you need to define your problem very clearly and model it using Excel. This comes with practice. Start by modeling sample problems you find in work / life and you will be able to master this art.
- Tweak Solver Settings: Mess with solver settings by clicking on “options” button.
Additional Resources on Excel Solver
- Excel Solver Introduction and examples from Microsoft
- Excel Solver Examples from Vertex42
- Linear Programming with Solver from Economics Network
- Excel Solver Tutorial & Examples [PDF] from Standford University
What is your experience with Solver?
First I must confess that I am still a partial solver virgin. I have used it during my MBA to solve some optimization problems. Then I never had the opportunity to use it while working. I still struggle to set-up models and find required solutions thru Solver. That said, I think solver is an excellent tool and very powerful.
What is your experience with solver like? Please share tips / ideas with us using comments.
27 Responses to “Are you a Solver Virgin? Watch this tutorial video …,”
I've been using solver for a lot of things for years now. I've used it to do non-linear regression analysis to predict liquidations of assets, and used it to optimize a schedule for a cub scout camp.
I love it, and used to use it almost daily, now down to about 3-4 times a month.
the downside, is that since I installed excel 2010, I can't get solver to work. it works in the beat version on my very slow desktop. it works in excel 2007 on my faster laptop. It doesn't work in excel 2010 on my faster laptop. I get a run error 453 can't find DLL entry Solv in Solver32.dll
You may also want to take a look at the Microsoft Solver Foundation for more complex modeling.
Thanks Chandoo for sharing this solver feature. I never know it exists..
There is a background sound about bird chirping and children screaming...that's very interesting., it made me feel that you spending your own quality time doing this video instead of spending time with family and enjoy your weekend. That's so much Chandoo.
Francis
I know that Jan 11 doesn't have 5 fri, sats and suns but on your video at 11:50 it shows 15 in the cell D10? Strange. Interesting video all the same. Thanks
Very good link Joe. I did not know about that.
@Francis... when you have small kids at home, any kind of recording is going to get their flavor. Thank you 🙂
@Ben.. I had a solver snafu while recording. I edited the erroneous frames, but I guess few must have been left out. Thanks for pointing it out.
First and foremost... i think you are driving me crazy with excel now.... i am not a pro but surely not a beginner.... (as i thought my self), but after going thru you site, which i am still unable to find how did i come across... i feel as if i am in kindergarden... but to the fact i have got a super-teacher in chandoo.org..... Its fantasic, marvelous, and what not... Thanks for your guidance.
Jul-2011 has 5 Fridays, Saturdays and Sundays.
Just wondering why the solution didnt show up this month?
Thanks so much for the fantastic work that you have done!!! appreciate the tutorials and useful tips on this website. Thumbs up!!!!
Excellent Knowledge Sharing Tips. Today morning while I was exploring Excel data input validation for obtaining contract labour data in a tabular form with input validation for importing to oracle database for expediting issue of gatepasses to the agency staffs engaged in the project site ( about 20,000 labours, 1000 pass is required to be issued for day ). I came across this excellent knowledge sharing site by Sh Chandoo. Till so far I have been using Excel as a mere worksheet and now I could discover its power for effective MIS generation for business problem. I will be cerainly benifting from Sh Chandoo ( my excel guru now ) and master excel is six month time. Thanks and regards
Came across your site while trying to self learn financial modelling in excel. This site has helped me so much...it has in fact contributed towards my getting a new job! I have been using excel for quite some time now and pretty comfortable with it. But with this site, I have come to learn so many new techniques - especially the charting ones are just amazing. Simple yet so elegant. Love your site. Keep up the good work!!
Rahul: you are right. Something missing in formulas.
Hello
i wanna know how to create macro in excel ms office 2003...
Hello Mr. Chandoo!
My Belated X-mas greetings & Advance Happy New Year - 2012 to You! and all your family members & friends! Thus far, your journey is AWESOME! Wow! you're really a Special breed for a specific purpose!
With regards.
Hey,
Awesome website / Guide to learn advanced Excel
Thanks a ton and wish u Happy new year to you and your loved ones
Regards,
Bajju
Rahul,
i agree with you. when you give 9 in the month it gives July 01, 2011 which satisfies the sum=15 criteria. and also try 29 which again satisfies the criteria.
so this is flawed....
i think it would be fun to find out the answer to this one.
Hi Chandoo, Infact i see that Mar 2013 has 5 Fri, 5 Sat and 5 Sun. But i am not sure why your formula gives 2019? For some reason solver is not working for me. 🙁 I have sent you my version to you in an e-mail. Wuld appreciate your help on this.
To Rahul, Ashish, Soujanya, Lakshmi
Take another look at your calendars and you will see that there are 5 Fridays and Saturdays, but only 4 Sundays in July 2011 and March 2013.
To Bill
Not sure what calendar you are using, I am using Gregrian calendar. (http://en.wikipedia.org/wiki/Gregorian_calendar) And it has 5 Sundays in July 2011 (3, 10, 17, 24, 31). Same for March 2013.
Rahul, et al. You are right. I am embarassed. The color color coding of the MS Office Outlook Calendar confused me. Thank you for your response.
After seeing few comments, I went ahead and reran the solver scenario and realized the Solver does not find the first occurrence but some occurrence. I think this is because solver uses an exploratory approach that can go in several directions and returns once it finds a solution. Let me explore this a bit further and if I cant find a better approach I will replace this example with something else that works well with Solver (like a simple optimization problem).
Thank you for your site.
If you want really to know about solver, go to solver.com.
It is not so simple program. (Microsoft install only a demo )
I hope you will enjoy this site.
Gabriel,
[…] Introduction to Solver […]
I am a bit confused. Doesnt the solver return a negative value in the objective cell? For example say I was trying to solve the simple problem minimize 10x^3+49x^2+12x-8=0 subject to the constrainsts x=-5.
The solver returns a value of 19 for x=-1. However, if we do a trial and error we would see that when x=-5, the value of the function is -93. I couldnt find out any method to tackle the problem.
@Rik
Why are you using Solver for this?
I would use Goal Seek
@Hui,
I know I can use a goal seek for this but was trying my hands on the solver with this and came up with this strange problem. Still not able to figure out.
Hi. I study applied math using VBA at Museu da Matematica (http://www.prandiano.com.br). Used open solver to schedule turns, reducing the amount of people working on telemarketing, optimizes pallet loads. Learned how to use Markowitz Model (modern portfolio theory), Benford's Law, simulated annealing, radial basis function, ant colony and some other models.
MISSING
Watch the Excel Solver Tutorial video: