How to Use the Solver Tool in Excel
HTML-код
- Опубликовано: 12 окт 2024
- In this tutorial, I introduce you to the powerful SOLVER Tool in Excel, Solver is an "Add-in" program in Excel. It is easy to "activate" as I demonstrate in this video.
The "Keys" to understanding how to effectively use SOLVER are:
1) Determine the "Target Cell" - which will return the result that you want SOLVER to produce. This cell MUST contain a FORMULA!
2) The "By Changing cells" that SOLVER will work with - These cells MUST feed into the TARGET CELL!
3) The CONSTRAINTS that you establish for SOLVER to observe as it finds a solution.
I think that you will enjoy my explanation of how to use this powerful and complex tool to generate the "real world" results that you are looking for in Excel.
Danny Rocks
The Company Rocks
www.thecompanyrocks.com
Prof Danny, may you never get tired to share your light...awesome..
Thank you for your very kind comments about my Excel tutorial on using the Solver tool.
I work hard to make my explanations as clear as possible. I am happy that you enjoyed my video.
Danny Rocks
The Company Rocks
I am so pleased that you enjoyed my tutorial on Excel's Solver Tool.
Thanks for adding your comment.
Danny Rocks
The Company Rocks
The best video on RUclips to understand Solver. Thanks !
thank you for helping me. i have a coursework tomorrow that's 20% of my grade and i really needed to learn this. my uni didn't provide much to explain on this so i am very grateful
My pleasure. Glad that I could help you.
Good luck with your exam!
Danny Rocks
The Company Rocks
Thank you for your kind words. I am pleased that I could make a complex topic - Solver - easy for you to understand.
My mission is, "I work hard to make it easy for you to understand how to use Excel."
Thanks for adding your comment.
Danny Rocks
The Company Rocks
My pleasure! Glad that you enjoyed my tutorial on Solver.
Danny Rocks
The Company Rocks
@thunderspotch
Thank you for adding your comment. I am pleased that you found my tutorial helpful.
Danny Rocks
The Company Rocks
Thank you. I have been trying to use a data table to solve a problem that I should have been using solver for. I was so confused before Solver.
Thank you so much for your kind words. I greatly appreciate you taking the time to add your feedback.
I take pride in - and work hard on constructing my lessons. My motto is, "I work hard to make it easy for you to learn how to get the most out of Excel."
I am happy that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
hi prof...loved it...its because of people like u we tend to keep knowing things at any instant...thank you
@3rdkompanie
My pleasure. I am happy that you enjoyed my tutorial.
Thank you for adding your comment.
Danny Rocks
The Company Rocks
Thank you! I enjoy teaching and receiving your comment encourages me to continue to pursue the path in life that I enjoy the most!
Danny Rocks
The Company Rocks
u save our life bro! Love ur video! We were gonna pull a all-nighter but ur video saved our life!
Thank you very much! I am so pleased that you enjoyed my Solver Too in Excel video.
Danny Rocks
The Company Rocks
Your ability to educate is phenomenal. Few people can do what you do. I really like your "Best Practices" coaching information. Your efforts will improve the Art of Educating by showing folks what High Quality Educational Material looks like. Keep up the GREAT work.... And thank you for the instruction on Solver !!
Thank you Erin -
I am pleased that I could help you with my Excel Tutorial on Solver.
Danny Rocks
The Company Rocks
Much Appreciated. Thank you. Clear, concise and thorougher introduction to Solver.
@andreh14
Thank you very much for your high praise.
I appreciate you taking the time to post your comments.
Danny Rocks
The Company Rocks
Thank you! I am pleased that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
Thank you so much Danny. This really helped me to do my college homework easiily.
thank you sooo much for doing this!! i have an exam on this tomorrow, this really helped!
Seen many examples of this sort but this example was explained very nicely
Thnks
you made me understandig thing that I consider too complicated to use, thank you!
Hello Petar -
Yes, for many computer users, the Solver Tool - and other tools in the Analysis ToolPak - are not installed by default. You do not need the original disks to "activate" these tool. As you pointed out, you simply have to select them and activate them.
Danny Rocks
The Company Rocks
Hi Danny, I am using the same version of Excel as in your presentation and my excel does not allow me to choose "solving options" i.e among GRG and Simplex.. kindly advise from where I can find that?
Thank you, It was extremely helpful
It is my pleasure. I am delighted that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
Thank you Aashay -
I appreciate your kind words. I am happy that you enjoyed my Excel Tutorial explaining the Solver tool.
Danny Rocks
The Company Rocks
You are a great teacher! Thank you, it was easy to follow. Thank you again, this video has been a great help.
Thank you so much for your kind words.
I am very happy that you found my Excel tutorial for the Solver tool helpful to you and your students.
Danny Rocks
The Company Rocks
Thank you Danny. I t was very helpful. Good advice about putting all the problem in words aside.
Well, I am pleased that you enjoyed my explanation. Since I cannot see what you are trying to recreate in your Excel Worksheet model, it is impossible to determine what is causing you this problem.
Solver is an advanced topic in Excel. In my experience, it requires multiple repetitions until the concepts become "second nature to you.
Ensure that you have selected the proper formula cell and the correct "by changing" cells. Verify that your Constraints follow a logical sequence.
hi dany if get the excel file it be good for me to understand
Hi Peter -
Thank you! I am happy that you enjoyed my Excel Tutorial on using the Solver Tool.
Danny Rocks
The Company Rocks
Thank you! I am pleased that you like my "motto" so much. No need to "steal it," simply apply it.
I greatly appreciate you taking the time to add your comments here.
Best of luck to you in the future!
Danny Rocks
The Company Rocks
Thanks for contacting me. As you can probably see, I have over 100 free Excel Tutorials here on my RUclips Channel. Explore them as you work towards improving your Excel education!
Good luck to you!
Danny Rocks
The Company Rocks
Great!
You have quite a keen eye!
Danny Rocks
The Company Rocks
Perfect! Exactly what I needed. Thanks a bunch!
Superb explaination .. very right pace... You are good at teaching.. thanks sir
Calvin -
My pleasure. I am happy that you enjoyed my Excel Tutorial about the Solver Tool.
Danny Rocks
The Company Rocks
Thanks sir but the issue is that sometimes you should provide practical file so that we can as well use it to follow through. Otherwise thanks
My pleasure! Thank you for adding your comment.
Danny Rocks
The Company Rocks
Thank you Danny, you helped me a lot!!
Thanks! I've been struggling over Solver and this was really really clear :D
Explained very well!
Thank you so very much for posting this.
Thanks, nicely explained.
Great Video, Even better if the download link of the workbook was there in the description.
Fantastic! Thank you for your effort and time sir.
@CENTAURSARATOGA
Thank you. I am pleased that you enjoyed my tutorial on Solver in Excel.
Danny Rocks
The Company Rocks
Thank you this was so helpful
Very nice briefing.
Great video! Thanks!
Thank you for the video, it is very helpful.
Does sir have videos on how to use the solver function to get intersection points on a graphs?
Hello Norman -
I am pleased that you enjoy my Excel Tutorials.
Re: Solver Tool. If you want to capture multiple "solutions" using Solver, I recommend that you make multiple copies of your original Excel worksheet and then run Solver multiple times using changing constraints, etc.
Another "What-if" Analysis tool is Scenario Manager which DOES allow you to capture, show, and report multiple solutions.
Danny Rocks
The Company Rocks
Thanks alot Danny Rocks that video is great
Wonderful tutorial! Thanks
Hello Vivek -
My pleasure. I am happy that my Excel Tutorial on the Solver tool helped you with your college homework.
Danny Rocks
The Company Rocks
Danny, thank you for a clear tutorial. One question: are the values in I5:J14 hard numbers or formulas? I am getting "Solver could not find a feasible solution". If you could share the spreadsheet in the video, it would be very helpful. Thank you.
You Sir, are just amazingly talented. *All hail SIR DANNY"
Aaron -
Thank you for adding your feedback. In my experience, I have found that every installation of Software tends to be "unique." Fortunately, with "cheap" disk storage, more and more "advanced" tools are now added to the default installations.
Danny Rocks
The Company Rocks
Great!
I an pleased that I could help you with my tutorial for Excel Solvers Addi in Tool.
Danny Rocks
The Company Rocks
explained very well... thanks for that
Helped me a lot! Thank you!!
Hey Danny,
Awesome tutorial. Works perfectly! I just have a quick question...I tried to record a macro for the solver tool so that I can quickly use it by clicking a button, but I get the following error: Compiler error: Sub or function not defined." Not exactly sure what I'm doing wrong. The solver works on its on but when I record a macro doing the same thing and assign it to a button, it doesnt work.
Great video Danny. I was applying your tutorial on one the problems but I got stuck where i have to find the maximum product mix at 85% of current sales forecast & at 90% of cost of goods sold. what's the way to go for it? thanks for the help
thumbs up, thank you for the tutorials.
This was very well explained. Thanks! :)
Thank you for the video!
I appreciate your video on solver, I inputed the same spreadsheet you described in the video and the and when I went to use the solver, it gave me an error that it was not able to solve the problem.
It says Solver could not find a feasible solution.
Could you help me with why I am receiving this error?
I am a student of Excel. While trying this data set I noticed that the number of units to sell or at least the total number of units to sell are not considered as constraints. I tried with some imaginary alternative numbers and even used zero and found that many alternative number of units to sell can be used to reach 56% profit target. even in the example used in this video the total number of units to sell was changed from 144 to 145. If it is flexible than individual number of units to sell can also be flexible and that should give a range of confusing scenarios to choose from. Please let me know what you think.
Is it possible to find particular cells whose total is between 100 to 200 with this data 42,43
,42,34,36,30,38,47,35,37,26,37,43,43,40,52,27,17,48,32,26
Really nice video, unfortunately you forgot about one vital thing. You missed one constraint. As you are selling "items" I assume that the numbers we are talking about are integers. That is what's missing in the constraints. Once the constraint is added you might find slightly different results.
I agree! The video is not showing the optimal solution because it is missing integer constraint. You also can't pretend you have the optimal solution by rounding the numbers directly in the cells. This thing must be done in the solver.
JeeP Brochu I agree
+JeeP Brochu Perhaps not the mathematically correct solution, but close enough. When I rounded the selling quantities to 0 digits and recalculated the rest of the table, the result was less than 0,04% off. Solver is not mathematically accurate and only calculates answers to an extent. It doesn't for example solve x^2=4 accurately to x=2. See for yourself.
+JeeP Brochu You can make a constriction that sold quantities be integers, at least in the newer solver, that is if you want to have realism to the example. However even that is not sufficient, as any solution that satisfies the GP%=0,56 condition, does not automatically produce the highest total GP. For that you would have to set the target as the GP cell and max it, while constricting the GP% to 0,56. :P
This helps a lot! Thanks :)
Wow dude you really helped a lot :)) Thank you so much
Good Job !
Thanks ever so much for your informative tutorials. I have a question regarding this tutorial however. Does the solver tool allow for more than one correct answer? If so is there a way to show all correct answers.
This is amazing, thank you very much.
thank for your reply sir.....For our project we need to normalize our excel data .We are using weka tool for our normalization technique...for that we have to convert our excel data to .arff format sir.....HOW TO CONVERT OUR EXCEL DATA TO .arff FORMAT.....thats what my doubt....can you pls help me
thank you for this video. it was very helpful!
I did a little research about the .arff format online. However, since I do not have any experience using this format - and from what I have read - I cannot help you with this matter.
Danny Rocks
The Company Rocks
I need Solver to accept formulas in the Variable Cells Line so that it can select only certain cells within a range. Is this possible? If not, is there an alternate method to achieve my goal?
- Also, why not put in I5:J14 as a single constraint instead of having I5:I14 and then also J5:J14?
clearly explained. thanks
This is was awesome.
Hello, thanks for the vid. But I'm confused by the 3 different inventory quantities: Qty on Hand, Units to Sell, & Units Left. Why 3? How do they relate to each other? Please help me understand. Thanks.
Hi Danny, my solver result for sale and qty left ended with decimals. Have I missed out some specific setting in the solver? Thanks
It would have been great to know how to add the Solver tool in the beginning
very well explained!
Hi, is there a way we can find the combination of the nearest sum if Target is not found? thank you
Hello -
I see that you did the mental arithmetic and subtracted the Unit Cost of $193 from the Selling Price of $350 and expected to see $157 and NOT $158.
The reason - "Rounding" of the cell values to NOT display the Decimal points.
Does this explain this "apparent error" in the cell result?
Sincerely,
Danny Rocks
The Company Rocks
Hi. I have a question. I created a macro (VBA) that emulates the solver by clicking on a button. The problem is that sometime I have to click 3-4 times before getting the last results. Do you know how to avoid this and getting on solution with one click?
Sir, you explanation about the matter is good. But the some calculation in your worksheet is wrong. Make me so confused to figure out
Hi, I have office 2007 it says Solver is aktivated and it has also a check mark, but it doesn't show up under Analysis. Any tipps?
I do not know the airpp format. If you can give me more information about it, perhaps I can offer you a suggestion to follow.
Danny Rocks
The Company Rocks
Hi Danny,
Following you since long & I genuinely appreciate your work!
I have a query in the solver, that would be great if you help me with this - As this is restricted only to 200 variables, I want to know how can I use this for more than 200 variables.
What I've is a column having amounts (+ve & -ve figures both) of which some are offsetting to zero & the remaining constitutes the total of all. Now what I've to do is to separate all those netting to zero & keep only those that are actually constituting total.
Kindly let me know what can be worked out in this scenerio ?
Great, thank you!
superb...
Can we have the worksheet to practice ?
can you please show how to solve very complex equations like equations with fractional powers in excel
Thank you :)
good video,