How to Use the Solver Tool in Excel
ฝัง
- เผยแพร่เมื่อ 15 ต.ค. 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..
The best video on TH-cam 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
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.
My pleasure. Glad that I could help you.
Good luck with your exam!
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!
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 !!
My pleasure! Glad that you enjoyed my tutorial on Solver.
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
Much Appreciated. Thank you. Clear, concise and thorougher introduction to Solver.
Thank you Danny. I t was very helpful. Good advice about putting all the problem in words aside.
you made me understandig thing that I consider too complicated to use, thank you!
Thank you so much Danny. This really helped me to do my college homework easiily.
Thank you, It was extremely helpful
Seen many examples of this sort but this example was explained very nicely
Thnks
Thank you very much! I am so pleased that you enjoyed my Solver Too in Excel video.
Danny Rocks
The Company Rocks
thank you sooo much for doing this!! i have an exam on this tomorrow, this really helped!
Thanks, nicely explained.
You are a great teacher! Thank you, it was easy to follow. Thank you again, this video has been a great help.
Thank you Erin -
I am pleased that I could help you with my Excel Tutorial on Solver.
Danny Rocks
The Company Rocks
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
Thanks alot Danny Rocks that video is great
Thank you this was so helpful
Very nice briefing.
Superb explaination .. very right pace... You are good at teaching.. thanks sir
Thank you! I am pleased that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
Fantastic! Thank you for your effort and time sir.
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?
Thanks! I've been struggling over Solver and this was really really clear :D
Thank you Danny, you helped me a lot!!
Perfect! Exactly what I needed. Thanks a bunch!
Wonderful tutorial! Thanks
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! 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
Great video! Thanks!
Explained very well!
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.
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.
Thank you so very much for posting this.
Wow dude you really helped a lot :)) Thank you so much
This was very well explained. Thanks! :)
Great Video, Even better if the download link of the workbook was there in the description.
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
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
Great!
You have quite a keen eye!
Danny Rocks
The Company Rocks
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.
explained very well... thanks for that
Hi Peter -
Thank you! I am happy that you enjoyed my Excel Tutorial on using the Solver Tool.
Danny Rocks
The Company Rocks
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?
This is was awesome.
Helped me a lot! Thank you!!
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
thumbs up, thank you for the tutorials.
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?
Good Job !
Hi Danny, my solver result for sale and qty left ended with decimals. Have I missed out some specific setting in the solver? Thanks
Sir, you explanation about the matter is good. But the some calculation in your worksheet is wrong. Make me so confused to figure out
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
This helps a lot! Thanks :)
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.
Thank you for the video!
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
thank you for this video. it was very helpful!
It is my pleasure. I am delighted that you enjoyed my tutorial.
Danny Rocks
The Company Rocks
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
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.
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
It would have been great to know how to add the Solver tool in the beginning
This is amazing, thank you very much.
Hi, is there a way we can find the combination of the nearest sum if Target is not found? thank you
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?
Great!
I an pleased that I could help you with my tutorial for Excel Solvers Addi in Tool.
Danny Rocks
The Company Rocks
clearly explained. thanks
You Sir, are just amazingly talented. *All hail SIR DANNY"
very well explained!
@CENTAURSARATOGA
Thank you. I am pleased that you enjoyed my tutorial on Solver in Excel.
Danny Rocks
The Company Rocks
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?
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
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
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
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
How can i solve a bidding problem with the Xcel 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
Can we have the worksheet to practice ?
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
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! Thank you for adding your comment.
Danny Rocks
The Company Rocks
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
I’m impress totaly
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 ?
Thank you!
Is this the Bob Ross, Painter and TV Host that you are comparing me to?
If so, I appreciate the compliment.
Danny Rocks
The Company Rocks
This video is very useful, but i am confused in this question ( it is not wrong it is there inn the ncert textbook grade 10)
The current profit situation ofa business owner is as follows:
Current sales - 85
Cost per unit - 72
Profit per unit - 12
Using the scenario managerin MS-Excel/ Open Office Calc, find the effect in the new profit in case of the following situations.
a. Sales = 70 and cost = 80
b. Sales = 90 and cost = 72
c. Sales = 85 and cost = 80
d. Sales = 65 and cost = 80
superb...
Instead of cells, can use rows instead?
@3rdkompanie
My pleasure. I am happy that you enjoyed my tutorial.
Thank you for adding your comment.
Danny Rocks
The Company Rocks
can you please show how to solve very complex equations like equations with fractional powers in excel
Kiitos Palion :) (Thank you very much) in Finnish language.
BR.
Saeed
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
thanks so much
good video,