Excel Solver & Goal Seek Tutorial
Vložit
- čas přidán 25. 07. 2024
- In this step-by-step tutorial, learn how to use Goal Seek & Solver in Microsoft Excel. With Goal Seek, you can find the result you want by adjusting an input value. Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell subject to constraints, or limits, on the values of other formula cells on a worksheet.
👋 Additional resources:
- Sample files to follow along: 1drv.ms/x/s!AmxrofZZlZ-whK4Xs...
- Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⌚ Timestamps
0:00 Introduction
1:49 Goal Seek
8:26 Turn on Solver
9:56 Solver Simple Example
12:11 Solver Advanced Example
22:49 Wrap up
📃 Watch related playlists and videos
- Playlist with all my videos on Excel: • ❎ How to use Excel
🚩 Connect with me on social:
- LinkedIn: / kevinstratvert
- Twitter: / kevstrat
- Facebook: / kevin-stratvert-101912...
🔔 Subscribe to my CZcams channel
czcams.com/users/kevlers?...
🎬 Want to watch again? Navigate back to my CZcams channel quickly
www.kevinstratvert.com
🛍 Support me with your Amazon purchases: amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert - Věda a technologie
🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: czcams.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
My CIS 112 class had me to watch this video but I ended up fell in love with your CZcams channel thank so much for your shares
Can you suggest how to make solver run for all the rows using loop?
👏🏻👏🏻👏🏻
This is an amazing training session.
Step-by-step, well designed, well delivered.
Incredible way to spend 23:33 minutes of your time to really learn something useful.
Thank YOU Kevin Stratvert
I like the simple approach you use Kevin. Excellent explanation of the different solver options.
Very meticulous way of explaining my friend,Keep it up.i come back to your videos all the time whenever I have problems on EXcel.Thanks for helping me out!
Kevin thank you so much, have learned alot from your tutorial, so am able to teacher my students computer packages confidently. Good work Kevin
Thanks Kevin! Very clear explanation! I didn't know about these features in Excel.
you are simply brilliant, the humor is on point. Thank you
Thank you Kevin for this excellent lesson tutorial!
A very friendly way of presenting Solver. Understood well. Thanks !
Thanks Kevin. Really before I don't what is Goal Seek & Solver in Microsoft Excel but you've explained it. Hats off Kevin :)
Haven't heard about these functions until now! Great!
Thank you, Kevin. Great lesson.
Once again Great Explanation from Kevin.
Thank you for your clear and concise explanation.
Very helpful Kevin, thank you mate!
Falling in love with your videos!
thanks!!
i actually was solving situations like these ... the manual way! Watch me impress my boss tomorrow. :)
Beautiful video. Thank you.
Thanks for the teaching!
Thanks for detailed explanation. Time to practise. ☺
A pretty clear explanation. Tks a lot!
Good clear explanation. Thanks.
good examples, clearly explained.. all good.👍
thanks for that.
Well Done!! Thanks!!
Thanks for this, simple o follow and understand
This is incredible!
Thank you, King!
Woah nice intro, giving me that retro feel.
Awesome Video. Going to play with your sample file to see how to include Day Old Cookies (i.e. current "acceptable" inventory) to see how I can maintain a full Product selection for your company - including the Fortune Cookie customer desires. Suspect that if money is the only goal - will need to reduce the variety of Cookies offered.
I REALLY APPRECIATE your Video's. Not only the content but your voice and diction. I'm hearing impaired and your one of the very few presenters that I NEVER have any trouble understanding the audio or the content your presenting. The sample files and the hyperlinks to sections of the video are OUTSTANDING.
THANKS a Million (Cookies). ;-]
Thanks Kevin, that was sweet.
May I suggest you do a sensitivity analysis of a project. Take investment, project implementation, cash flows, discounts , profits, & determine: Rates of return, payouts, etc
Getting an intuitive understanding of how changes impact complex calculations is very difficult in practice. This would make a great video.
Had no idea this existed. Fabulous.
Hi Kevin, great video first of all. Very clear explanation and concise. I'm just wondering though - what if there's multiple solution to the problem? Is there any way to display this?
You weren't kidding about evolutionary taking a while, great introduction as always.
Amazing !!!!!
Thanks in a million.
Wow Kevin Thanks
Veey Nice .You explained it in a very simple manner..Thank You Very much
Very ncie and informative
Very useful video thank you
I have a question, what's the difference between evolutionary and GRG non-linear with multistart optiion? Form what I understood they do the same thing.
Thanks Kevin.
Nice touch with the new intro.
Kevin, the man we didn't want, but needed
Affflack
Thank you Kevin ..
Say what??? I had no idea that Goal Seek or Solver even existed in Excel. Yet another Kevin video I need to bookmark for reference. Thanks Kevin!
Many thanks very helpful
The free version of solver comes in Excel limits the number of variables that it will take. So have this constraint in mind when you plan to use it.
Correct: 200 variables and 100 constraints.
After that you’ll have to pay up.
Optimisation Solvers are big business!
Are there any "PREMIUM" Solver version? If yes, where find???
ilove you man, you really help people
for the first time I have understood the goal seek and solver. In my mathematics class we used to call id linear programing an used simplex algorithim
Superb!!
Very nice tutorial.
I don’t know man, I have achieved a lot of your videos Kevin and this one is also for the books 😂. Thanks for all you do.
Love that intro though! That transition from the intro to you telling *"Hello Everyone, Kevin Here"*. That's just AMAZING after all!
Nice one.
Hi great video. How do you make solver update automatically when you change a value that is used in the equation, not an input value that you are asking to calculate but a constant that you set manually. How you can get all of the solutions visible that will fulfill the constraints?
Well explained
Thank you
Love it.
Will you do a "How to use" the "Szenario Manager" ?
it would be very interesting; if you can come up with a good use case :-)
BR from Switzerland
Hello Kevin, First, many thanks for the brill videos. Second will you be covering anything from the dark side (Macs)... databasing?
Thank you... Now I'm actually craving cookies haha
18:40 " I don't wanna back any negative quantity cookies....unless I'm starting up a weight loss program...." OMG LOL I'm dead.
Mmm! Kevin Cookies, I want some, where can I order? Fun videos as always. I love Excel, so much Algebra math help built in.
Kevin, if financial analysis including accounting is not your strength, you should partner with someone who is, and as a team create these excellent Excel tutorials on how to use Excel advanced tools in the field of finance and accounting. Many people, including your subscribers, are intimidated by the number crunching involved to do budgeting, something that they and everyone should do as part of their adult life. Having a working budget is important to keep oneself financially solvent. I think for a noble goal as a CZcamsr, you should expand to help people better their lives using powerful tools like Excel. Using running a fictitious cookie company is fine and dandy, but you can become a more successful content provider here with real life applications. You certainly have the natural and hard-earned talent as an explainer of technical topics with easy-to-follow lessons. Perhaps it’s time to up the ante to more practical real-world lessons?
There's another similar JS add-in called "Analytic Solver" which is easy to find in add-in store: Excel->Insert->Get add-in
Good video. Thank you! Can I activate the solver from the VBA, after I created it?
I literally started eating cookies first thing in the morning because of this video
First nice video
When is the coding video coming pls tell
congratulation..you just solved how to add solver..hahaha..anyway, nice video kevin..
Best
Hi Kevin, Thanks for loading such helpful videos.
Could you please solve one problem - How to reduce the time frame - this annoying windows 10 pop volume control panel stays on screen. That comes on the top left hand corner. It just stays on for soooooo long. This is a big nuisance. It want the pop up to come but only stay for short time. Can you plz help with this. Thanks
Thank you sir, question is There a way to copy and paste The solver calculation to other spread sheet , without needing to repeat ALL The process?
Thanks for video. Is there a way to do goal seeker automatically? how to say, to be real time. when other values in the sheet change, goal seeker update itself automatically.
Bro love from pakistan
Great material Kevin.
QUESTION: When recreating your exact initial SOLVER example; Why do I keep getting a message saying "CELL REQUIRES A FORMULA," when a formula is clearly there?
CONFUSING AND FRUSTRATING.
Thank you.
Hi Aldo, I found your video very easy to understand and I have immediately subscribed to your channel. However, I was not able to solve my simple problem. I need to calculate what my revenue should be in a range of 4 years in order to arrive at a net profit of $3,000,000, given some non-linear fixed costs. I am trying to use the solver function because I need to know the value of my revenue for each year. When I run the solver function it says that "Solver can not find a point for which all Constraints are satisfied.", but I did not put any constraints. I also tried to enter the constraints for my fixed non-linear costs, but I got the same error message. What am I doing wrong? Thanks, Paolo
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?
👍 is there an equal for Google sheets?
Can you make a video for how to get weighted average contribution margin using solver?
and btw, it was a greattttt video.thank you
Kevin- im looking to use solver for a specific headcount optimization problem at work, let me set the stage and perhaps you can help. I have 41 stores (column A store ID), traffic (column b), FTE budget (column c) and traffic per fte (c/d). the goal is to say, given a finite budget (lets say 400 for simplicity, aka the SUM of all stores budget), we want to optimize traffic per FTE (the calc in E at the store level by saying no store had more than 900 swings per fte). is this possible to say, hey move budget numbers around (keeping the 400 total budget) to solve for a range of calcs?
I hope you found a solution already, if not try these steps hope it helps.
To solve your requirement using Solver in Microsoft Excel. Here's how you can do it:
1. Enter the data for the 41 stores in columns A, B, and C.
2. In column D, calculate the traffic per FTE for each store by dividing the traffic by the FTE budget.
3. In column E, create a binary variable for each store, where a value of 1 indicates that the store's traffic per FTE is greater than 900, and a value of 0 indicates that it is less than or equal to 900.
4. In another cell, calculate the total budget by summing the FTE budget for all stores.
5. In yet another cell, calculate the total number of stores that exceed the 900 traffic per FTE threshold.
6. Select the Solver add-in in Excel and set up the Solver Parameters as follows:
Set the Set Objective cell to the cell that calculates the total number of stores exceeding the threshold.
Set the To Value to 0, as you want all stores to have traffic per FTE less than or equal to 900.
Set the By Changing cells to the range of FTE budget cells for all stores.
Select the GRG Nonlinear method as the Solving Method.
Check the box for "Assume Linear Model."
Check the box for "Assume Non-Negative."
Check the box for "Use Derivatives for Gradients."
7. Click the Solve button to run the Solver.
8. The Solver will adjust the FTE budget for each store to minimize the number of stores exceeding the 900 traffic per FTE threshold, while keeping the total budget equal to 400. The result will be a new set of FTE budgets that optimizes traffic per FTE.
How do you figure out the demand?
Can you make solver tutorial with case study using bin and dif constraint?
I dont understand 😢
well , can i use this tool to solve sudoko or i need vba code ??
How Can I download this sample for test in Excel??
Thanks you
Please make videos about microsoft Child account ..plzzz
👍
😂😂😂"I know these cookies are very distracting"
What if my data tool does not have "what if analysis"
Hey Kevin,
Great simplex example for how to use Solver! ;-)
Please note that there’s (a lot) more to the underlying solvers algorithms than what you showed.
Especially the Simplex (LP - Linear Programming) method is more intricate than your straight line suggests.
BTW: it was invented by an American (USAF) back in 1947, so you should be a little proud about it. :-)
Also, only Simplex is guaranteed to find the global optimum solution. That’s one of the beautiful things about LP.
The requirements for it’s use: all equations (objective and constraints) need to be linear in the variables.
FYI: all your examples were linear.
Then again, truth be told: “Life is a Constrained Optimisation Problem”, but it’s non-linear and often not even smooth.
That’s why life chose the Evolutionary Solver.
...Just think about that one for a minute and let it sink in. :-)
See you on the next one! :-)
what did I just read.
can you give example for non linear variable that similar to this case sir? :)
Hello Kevin, I always turn to you when there is something I am struggling to solve. I am trying to produce a solution for scheduling a workforce when they have different skills and different work hours and different departments. Can you point me in the direction to achieve this. I rather think that solver is the answer but don't know how. Any help would be gratefully received,
Can you add notes to Fortune Cookies that say "Life is happy when you eat chocolate chip cookies"
Kevin make a tutorial on Python programming
First
Second
00:09 Excel goal seek allows you to solve for an individual variable.
02:19 Using Excel Solver & Goal Seek to solve business questions
04:20 Using Excel Solver and Goal Seek to find the break-even point
06:21 To become a cookie millionaire, one needs to sell 404,000 cookies.
08:13 Solver is an add-in for Microsoft Excel which allows you to solve for multiple variables and constraints
10:17 Excel Solver allows for duplicating the actions performed in Goal Seek.
12:07 Solver can be used to find optimal answers and maximize profits in complex scenarios.
14:10 Using Excel Solver to find the optimum combination of cookies to make for maximum profit.
16:06 Maximize profit by finding variables that lead to maximum profit.
17:59 Selecting cookie quantities based on total demand and constraints
19:46 Excel Solver offers different models for finding optimum values based on the data type.
21:44 Select GRG Non-Linear as the solver option
Plz make a video on ransomware. i have in my computer plz tell me how can i recover my data
You mean I could have cheated in Algebra class using Excel to solve the “train” problem?
one random question. Do you actually like baking cookies? xD
I like eating them the most 😁
Hello handsome.
Ddddddddddon
Talk to much
I can't solve my problem with solver. I wanted to set several entry-exit points based on predefined market price changes, but the solver failed to give me the right variables to get the maximum value. Though manually I can solve it. Can you help me to get the best result? I can share my template with you if u send me ur email address.