🏫 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: th-cam.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
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
As usual Kevin, in the most simplest process you have delivered on Goal Seek and Solver. Brilliant!! And you made it very practical with the Cookie company example.!! Incredibly amazing!!
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!
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
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). ;-]
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?
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 TH-camr, 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?
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.
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.
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
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
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?
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
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.
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?
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! :-)
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.
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,
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
🏫 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: th-cam.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
My CIS 112 class had me to watch this video but I ended up fell in love with your TH-cam 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
you are simply brilliant, the humor is on point. Thank you
thanks!!
i actually was solving situations like these ... the manual way! Watch me impress my boss tomorrow. :)
As usual Kevin, in the most simplest process you have delivered on Goal Seek and Solver. Brilliant!! And you made it very practical with the Cookie company example.!! Incredibly amazing!!
Kevin, the man we didn't want, but needed
Affflack
I like the simple approach you use Kevin. Excellent explanation of the different solver options.
Kevin thank you so much, have learned alot from your tutorial, so am able to teacher my students computer packages confidently. Good work Kevin
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!
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.
You weren't kidding about evolutionary taking a while, great introduction as always.
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!
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). ;-]
A very friendly way of presenting Solver. Understood well. Thanks !
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.
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!
Thank you for your clear and concise explanation.
Had no idea this existed. Fabulous.
Once again Great Explanation from Kevin.
Love that intro though! That transition from the intro to you telling *"Hello Everyone, Kevin Here"*. That's just AMAZING after all!
It's just a damn intro
A pretty clear explanation. Tks a lot!
Thank you Kevin for this excellent lesson tutorial!
Falling in love with your videos!
Thank you, Kevin. Great lesson.
excellent lecture. thank you 🤩
Veey Nice .You explained it in a very simple manner..Thank You Very much
Thanks for this, simple o follow and understand
Thank you, King!
Very helpful Kevin, thank you mate!
Thanks for the teaching!
Thanks Kevin! Very clear explanation! I didn't know about these features in Excel.
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
This is incredible!
Thanks for detailed explanation. Time to practise. ☺
ilove you man, you really help people
Nice touch with the new intro.
Thanks Kevin !!
Thanks in a million.
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?
Thanks Kevin.
Thank you soooo much!
Beautiful video. Thank you.
Good clear explanation. Thanks.
Thank you Kevin ..
Well Done!! Thanks!!
Woah nice intro, giving me that retro feel.
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 TH-camr, 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?
Many thanks very helpful
Very nice tutorial.
Good video. Thank you! Can I activate the solver from the VBA, after I created it?
Thank you... Now I'm actually craving cookies haha
good examples, clearly explained.. all good.👍
thanks for that.
Very ncie and informative
Wow Kevin Thanks
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.
Well explained
Superb!!
Thank you so much sir. I would give it 1k likes if could
There's another similar JS add-in called "Analytic Solver" which is easy to find in add-in store: Excel->Insert->Get add-in
thanks man
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.
Amazing !!!!!
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
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
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???
Thank you
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?
Can you make a video for how to get weighted average contribution margin using solver?
and btw, it was a greattttt video.thank you
I literally started eating cookies first thing in the morning because of this video
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?
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
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.
Hello Kevin, First, many thanks for the brill videos. Second will you be covering anything from the dark side (Macs)... databasing?
First nice video
When is the coding video coming pls tell
congratulation..you just solved how to add solver..hahaha..anyway, nice video kevin..
Mmm! Kevin Cookies, I want some, where can I order? Fun videos as always. I love Excel, so much Algebra math help built in.
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.
Nice one.
Can you make solver tutorial with case study using bin and dif constraint?
I dont understand 😢
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?
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? :)
Please make videos about microsoft Child account ..plzzz
Bro love from pakistan
👍 is there an equal for Google sheets?
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 Can I download this sample for test in Excel??
Thanks you
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,
well , can i use this tool to solve sudoko or i need vba code ??
How do you figure out the demand?
Best
What if my data tool does not have "what if analysis"
Kevin make a tutorial on Python programming
😂😂😂"I know these cookies are very distracting"
Can you add notes to Fortune Cookies that say "Life is happy when you eat chocolate chip cookies"
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?
First