How to Optimize Your Daily Fantasy Football Lineup with Excel and VBA | Jordan Williams | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

How to Optimize Your Daily Fantasy Football Lineup with Excel and VBA

teacher avatar Jordan Williams, Excel/VBA Teacher for Fun, Useful Projects

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

14 Lessons (48m)
    • 1. Introduction

    • 2. Data Input

    • 3. Data Validation

    • 4. Filter Data Paste

    • 5. Filter VBA

    • 6. Button Assignment

    • 7. Transfer

    • 8. Transfer1

    • 9. Formulas1

    • 10. Pivot

    • 11. Finishing Touch

    • 12. Formulas

    • 13. Sorter

    • 14. Solver

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

This class is for all skill levels in Excel as it is a step by step process.
This project is designed to teach you valuable skills in Excel and VBA while working on an interesting project that will impress your friends and family. I am not a proponent of spending hours on definitions or really trivial things that have no real world use. I believe in doing the work to learn something.
In this class, you will use IF formulas, Data Validation Tools, SumProduct formulas, Macro Recorders, and VBA editing. This class is designed for those that are interested in the functionality of Excel and not the theory.

Meet Your Teacher

Teacher Profile Image

Jordan Williams

Excel/VBA Teacher for Fun, Useful Projects


My name is Jordan Williams. I have a Master of Arts in Economics from the University of South Florida (2014) and both a Bachelor of Arts in Economics and a Bachelor of Science in Business Administration from Florida Southern College (2011).
By day, I'm a Revenue Forecaster for an electric and gas utility. I'm still a young professional that is constantly learning, but what I have definitely come to learn is that pairing a knowledge of Excel/VBA with business acumen makes for a great skillset... It also makes life so much easier because you're able to accomplish tasks at a much more efficient and effective pace.
Outside of that, I'm a normal sports loving guy that uses Excel in my everyday life. I recently wrote a Kindle Book called How to Optimize Your Daily Fantasy Football... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction: This is an exciting project where you get to take fantasy football data and integrated with the Excel to create an optimization problem with a budget constraint. This is a great project Teoh brag to your buddies about or or use against your rivals. Um, I brag to my family about it all the time. It's, uh, it's pretty interesting. It definitely is a good conversation starter. So what you'll get to learn in this project is you'll be working with if formulas, some product formulas will get into pivot tables a little bit, and also you'll get an opportunity to work with V B A through the use of macro recorders, and you also go line by line right in a little bit of the V A. So I hope that you enjoy this project, and if you have any questions, please feel free to reach out to me. 2. Data Input: to start this project. What you wanna have is you want to open up an excel worksheet since we're going to be a inserting VB a code into the project wanna go ahead and save the file as a macro enabled file? So what you do is you click on file, go to save as we'll choose a place where you want to save it. Click on the drop down and select Excel Macro enabled workbook. I'm gonna go ahead and just save it to my desktop. Um, here, you can go ahead and change the name. I'm gonna leave it as book one for the time being. So what we'll need is will need four different tabs and we'll get to naming them a little later, so I'll go ahead and put the four tabs in there. The 1st 1 will be named input. So you click on rename. I've been input. The 2nd 1 is we're going to use to filter our data later on, so we'll name it. Filter. The 3rd 1 go ahead named Optimal. And the 4th 1 will be named pivot. So we're gonna go ahead and go back to our input tab now This is where you're going to go out on Google or Draftkings or Fanduel or wherever you can find a list of fantasy football players with their position, team, name, salary and projection. I've already done that, so I'm going to go ahead and grab the data that I have and paste it into my input tab. 3. Data Validation: So, as you can see, I have all of my players in my input tab. So now I'm ready to head over to the filter tab here. What we're going to do is we're going to entrance and data validation rules, which essentially equates to a drop down. Now, as you can see already have the data in there, I'll show you how to do about three of them. And then I assume that you can take it from there. It's important that we extend all the way out to call him H and go down to grow four. What? And it's best if you do this in alphabetical order. I chose to use the 2 to 3 letter abbreviation. So what you do is you click and sell a one you want to navigate to your data tab. Click on data validation Data validation. From this drop down, you'll see the list. So go ahead and click list. Then what you want to do is type in apostrophe comma a rz. The apostrophe allows you to have cell when equal to blink. So then you press OK Then you do the same thing for Selby one, so you'll cook on data data validation dropped down data validation. Again, you'll select the list. This time. You'll type in apostrophe comma a TL for Atlanta, of course, so you'll follow this for the rest of them. I'll show you one more so you'll go to Cell C one. Go to your data tab. Data validation Data validation list. Apostrophe comma B A L for Baltimore. And now you can see that I can change this to blink, or it can change its Baltimore. This will be important when we try to filter out teams. 4. Filter Data Paste: So now that we've entered our data validation rules, which equates to the drop down for all 32 teams, we're going to go ahead and record a macro in order to get the data from the input tap into the filter tab. Now we could go into V B A and bright it line by line, but that's pretty boring, and I'm all about functionality and practicality. We'll get into VB, a line by line and a little bit. So down in your left hand corner, you should see a little square, and it has a circle in the upper left hand corner of it. That's to record a macro. Go ahead and press that button and we can go ahead and name the macro data input. Don't so press? OK, so then go ahead and select a five, drag it out to H five and then all the way down to Row 1000 and five. This will allow you to have upto 1000 players, and it will get rid of any leftover players just in case you want to feel through things out and what not. You definitely don't want to have leftover players in there So now that were selected through Row 1000 and five. Go ahead and click. Delete on your keyboard. Select a five once again. Now we go to the input tab. Select a one, drag it over to E one, then on your keyboard. Go ahead and hit. Shift and down. Now with your mouth, you'll want toe right. Click on the data click. Copy. So, like the filter tab. Once again, select a five once again. And then right, click and paste the data in there. From there, we could stop with this macro. So go back down to the left hand corner of your Excel spreadsheet. You should see a little square. Go in, press that square button. Now we're gonna go ahead and go into the code and drag it from the module up into the worksheet itself. So you'll click on your developer tab on your ribbon to the far left, you'll see visual basic. Go ahead and click that when you record a macro, it goes into your module, so you may need to extend this out. So double click on module one. Then we recorded data input. So we'll go ahead and copy that and we'll click on this workbook so you can see already have data in there. But that was by mistake, so I'm just gonna paste over the top of it. All you would have to do is just paste in there by right clicking. Now, these little apostrophes, they just, uh, closer for comments. So that doesn't actually read the code you can put in there. Um, pretty much anything you want if you put an apostrophe. So I like to put developed I Okay. I like to put that it was developed by me going to enter and you'll see that it changes green from there. We're gonna go ahead and reset it. So we'll click on this button here, and then we'll go back to our excel. So to the forehand left, go ahead and click that and 5. Filter VBA: So now that we've entered in our players and we have our data validation rules, I'm going to show you. You know, we have this drop down here. I can change his blink. I can change it back to Buffalo. Same thing goes for the Giants and every other team. Um, what we're going to do now is we're going to go into VB A to filter out players that we don't want in our fantasy football lineup or actually teams that we don't want in our fantasy football lineup. Let's say that you're playing in the A tournament. That's a Sunday night and Monday night. Well, obviously not all 32 teams. They're going to be playing that night. So what you would end up doing is the teams that are not playing that day or you don't want in your fantasy football lineup. You would just change those to blink, so go back to your developer Tab Goto. Visual basic type in sub delete open parentheses, close parentheses, press enter and I like to press enter a few times because I like to leave space between. So this is the code that we're going to use to go ahead and filter out the players that we don't want. Slash the teams that we don't want. Go ahead and within your supper team, type in the code the way I have it in the exact way here, this first piece of code is going to fill throughout the Arizona Cardinals. So, as you can see here, we have a one selected. That's because in the filter toe we put a RZ in cell a one. So here are down. Here are ways that HRC could be abbreviated or a r I our for theirs on the Cardinals could be abbreviated. We want to make sure that whatever is in our data or input tab, the team name corresponds Teoh the different code that we have in here. So this is just accounting for different ways that they could potentially be abbreviated if there's If you wanted to add more of them, all you would have to do is highlight one of these. Copy it, go to the end and press enter, then paste it in there. Then you would look at these right in here and let's say that they could be abbreviated capital, a lower case. Our local Z. So really, you just want account for the different ways that you believe there is on the Cardinals could be abbreviated. So I'm gonna do tomb or of them, you're going to have to end up doing a Lot 32. This is the tedious part of coding. You've already mapped out the plan, but now you've got to actually do the work. I don't like the press enter a few times a zoo saw. I did highlight from end if up to if and I copied it, then I'm gonna go ahead and paste it and separate them so I can understand the code a little bit better. So now we need Teoh Phil throughout the Atlanta Falcons. Now we put the Atlanta Falcons a TL and sell be one. So we need to change this to be one in the range. We also need to dimension this as a different name. So we know that this is the Falcon. So let's just keep it simple. Changes to a t l so anywhere that says a rz, we need to change it to a t. L nice and boring stuff here, but it's a necessary evil if you want to be able to automate things. Sometimes you have Teoh do the born stuff. So as you can see him still changing a few things here, um and this will be the same for all 32 teams. So again, I'll show you one more team after I show you this one, and I hope that you can take it from there. So now we need to think of the different ways that Atlanta could be abbreviated. We know one of them is a TL. All capitalized. Let's say another one would be a t l not capitalize. Well, I can't really think of any other ways that you would abbreviate Atlanta. So I gotta go ahead just for movies, too. No need form. At least. I hope not. So now I copy from end if all the way up to if press enter a few times so I can see space. Now I'm gonna do Baltimore. We know that we put B A l in cell C one. So will change that. Now we need to change everything from a t l c B a l. And there's good reason for this and I try not to get too far into the definitions of things. But you're just dimensions dimension ing a different name here, so Excel could recognize the different set of code. That's a simplistic way to put it, a state of a form all about practicality and functionality. You really don't need to know the definitions of this stuff to do this type of work. So I'm mostly going to go in here and change it to be ill. And again, I can't really think of a ton of names for for the way Baltimore would be abbreviated other than a lower case and an upper case one. So what you're going to end up doing is doing this for all 32 teams. The last scene that you'll do will have a range here of H four, and I believe that was or Washington. So once you completed with that, we'll get into creating buttons to run these codes. 6. Button Assignment: So now that you've completed all 32 teams, as you can see that Washington here, down at the bottom, we're gonna go and go back to excel. We're going to go to the filter tab. So then what we're going to do is up up the ribbon. We're going to press insert. We're gonna insert a text box. Um, I want to put it in j two to let's say around que for So we're going to go ahead, just enter in the textbooks. Let's enter the word in it and you can play around with the format of it. I'm going to go and put it in the middle, make it bold, and I mean, you could change the size of the wording. You can change pretty much anything you want, Teoh in it. I'm almost going to end up changing the color. You do that by right clicking on it and pressing format shape. So I'm going to fill it with, um let's just say orange. So the next thing I'm going to do is right. Click on it again, and I'm going to sign a macro to it. This is the code that we've already written so yours would end up being this workbook Data input. So we've assigned a macro to this button. Now we're gonna go and do the same thing. Oh, well, insert a shape which would be a text box and let's go and just put it a That works. And let's call this one filter. So I'm gonna bold it again, change the size of it and push it towards the middle. Um, mostly going to fill it. And this time we'll make it yellow. You can change it to whatever color you want. You can make the lines around it black and all of that. Whatever you want to do to it. So I'm going to right click again in it a sign of macro. And this time it's going to be this workbook delete, because that's what we name, the one that the code that you just completed. So now you can go and press input and see what happens. What should happen is you take all of your players from your book tab and it'll put it on your filter tab so fast you didn't even really see it happen. So let me go and show you you can see it Deleted everything out of there. I'm gonna press input. Boom! It's all there. So now the next thing is to show that we can filter out, um, teams, as you can see here, A J Green Cincinnati. See him right here. I'm gonna go ahead and say, Well, I don't want a J Green or I don't want Cincinnati on my team, so I'm gonna change them to blank press filter. A J. Green is going as well as every other Cincinnati player. Now, if I want to bring him back, I bring back Cincinnati on I press input again. 7. Transfer: Now we have another macro that we need to record. We need to get the data from the filter tap to the optimal tap. So we're gonna end up placing the player, team position, salary and projection on the optimal tab. So we're going to go ahead and press that recorder button once again on Let's name this one . Transfer. Click. OK, now select the optimal tab and highlight columns A through E on your keyboard. Press delete. This will give us a fresh start. Select a one head back to the filter tab. Now highlight from a five e five, and then we're gonna go all the way down to 1000 and five. Once again, this is probably overkill, but it's better safe than to be sorry. So we're going to copy this data right? Clicking. Selecting copy. Select your optimal tab. Once again, Select a one once again. Right. Click and press paste. Select a one once again. Then we're going to head back to the filter tab, Hit the escape button on your keyboard, select a five and we could stop recording the macro now. So from here will do the same thing. Will enter another concert, another text box. We'll select shapes. Sex box right there looks fine to me. And let's just name this one transfer once again, I'll put it in the middle size 16 or 18 or whatever size you want it to be. I'm gonna format shape and I will fill this one with green or lime green. Then I'm going to assign a macro to it. And this one happened to be transfer. Now, I could do that, but I actually need to do one more step here. I need to go into developer visual basic and go back to that module one because we recorded the macro, it went directly to module one. So let's go ahead and copy it from module one and put it in this workbook. I like to put everything in this workbook, but that's not inserted inside of the other subroutines of fine in sub. And then hit enter a few times after you find the end evidence up, paste it in there, you can get rid of these apostrophes. They're all just comments you can put developed by you once again. So then hit, Reset will have backed or excel screen. Now we can right Click on transfer sign Macro. And it should be this workbook transfer. Don't worry about all of these other ones that I have in there is because I have another workbook open. If you have other workbooks open, it'll all pop up in there. So just follow the ones that I've mentioned so far. Once mentioned so far, this workbook data input this workbook dot delete and this workbook dot transfer So we'll go ahead and click, OK, and then we'll move on to the next video. 8. Transfer1: currently on the filter tab. Uh, if we go over to the optimal tab, you'll see that it's blank. So let's go and head back to the filter tab. Let's press transfer. Now all the players should be transferred over to the optimal tab. And as you can see, they are. So now we have a little more set up that we have to do so and so f one. Let's type in ratio. G one. Type it a rank. Now we'll start entering in position, so Q B Let's go running back, wide receiver, hide in in defense. The next thing we're going to do is type in salary. What type? Been projected points. It was good and send that out. So I'm changing the format of that. I didn't want it to be underlined. Now we're going to end up being constrained by q b R B. You are. I didn't on DST. We're going to make the assumption that within our league were only allowed within. Our daily lineup were only allowed. One Q B two running backs, three wide receivers, one tight end and one defense 9. Formulas1: We need to set up a few more formulas to set up the constraints, Um, and so P. One good and type in this some product formula. Basically, what is going to do is it's going to multiply your ring, which is currently zero but will eventually show ones and zeros again. It's binary on. Then it'll multiplied against your salary, so we'll make sure that we can keep you under that $50,000 salary. So please type in this some product formula that you see appear and type it in exactly the way you see it. So it'll be down to G 201 and d 201 going to do a similar idea and sell P to its ah, again, some product. But this time we're going to be multiplying the rank and the projection. So this is what we're gonna end up doing, is trying to maximize its projected points based on the player's salary and keeping you under budget or putting your right at budget. And it's gonna pick the eight players based on these constraints here. So the goal is that that's the ultimate goal is to maximize your projected points and keep you at or under budget. So the next thing will dio let me go and go back to P to make sure that you get this some product formula alcohol on the p three. Um, we're just going to sum up the rank here and make sure that we that we have eight players. So you're essentially just saw something. Column G. We're gonna do the same thing for Q B's. They're gonna do it for column h and basically just constraining us toe. One Q B now p six. Well, some up column I and and constrain ourselves to two running backs and you'll see on a few more videos that when we get ready to do actually set up the optimization problem. You'll see exactly why we're doing this now. So now we're going to constrain ourselves to three wide receivers were going to sum up this section or Colin J. We'll constrain ourselves to one tight end by something not going K. And one defense last special seems by summing up calm. L Now this is for more of a fanduel or I'm sorry. More of a draft king set up, I think, on her fanduel they have kickers. Similar idea. You would just add a kicker into column em and then ah, here and in 10. Put k type in one here. Oh, and then the same type of formula here in P. However, you would push up, um, 029 or push this up to nine. And I think this would be, like 60,000. So similar idea. But let's just stick with the way I think of draftkings type of system would be set up. 10. Pivot: So the next thing we have to do is set up our pivot tables. I'll show you how to do roughly the 1st 3 of them and then from there I'm sure you can handle it. So we'll go ahead and go to art. If it tab, we will on our ribbit or make sure you select a one on a ribbon, we're gonna go ahead and press insert. We're going to press pivot table. Then we're gonna go back to optimal. And we're going to select from l or a one all the way down to L 201 and make sure that your location is still a one, which is fine and that it says existing worksheet. You don't want to open up a new worksheet every time you really want it just to be on your pivot tab. So breast, OK, now what we're going to do is we're going to set up our filters and, uh, get our quarterback in there. So we need to choose rank and we need to you choose. Actually, I apologize. We need to choose Q B and then we need to choose player. I will change this Q b up into our filter. And then we will change this from select multiple items to just one. So it tells me that Cam Newton is my guy. So then I'm going to click in sell D one and I'll go back to insert pivot tables, making sure that my existing worksheet and location is fine. I don't want to have that new work she open. Go back to optimal. And I repeat the process down 2 to 1. So it's a one to L to a one for us. Okay, so this time I'm gonna choose running back. I want that in my row. I'm sorry. Want that in my filter? And I want the player name in my row. That's right. Sorry I confused myself for a second. So then I'm going to go and change this toe one, and it tells me to pick Cam Newton and Adrian Peterson. If you repeat that process by clicking and g one and then changing everything to your wide receiver and then Jay one to your, uh, tight end, you get the idea. So what we'll do is we'll go back to optimal scroll back up and you remember these little things that we entered here, quarterback running back. You're gonna hit the plus sign head over to pivot, Click on the name you do the same thing for the running bets and then so repeat the process for the wide receiver and the tight ends and the defense of special special teams. And in the next video show you how to automate the solver and refresher pivot tables so your optimal lineup always appears in this shaded area. 11. Finishing Touch: So as you can see, I've created the rest of my pivot tables and put my plus symbols in there to add the players. You can see this is the optimal lineup that it spit out for me with a projected total of 167 points. The one last thing we need to do to automate the silver as well as refreshing the pivot table we'll have to do is we'll have to go into the developer tab. Click on visual Basic. Make sure we're double clicked on this workbook. We'll head into tools and references. Need to add the salt for reference. So check that box and click. OK, so we're gonna head back to our excel and we're going to record another macro so we'll go ahead and call this one optimal press. Okay, Head to the data tab. Press your solver again. Press solved thistles Gonna pop up every time so you will have to press OK on this portion , then One last thing you'll dio is you go over to your pivot tab, go to analyze on your ribbon. Select the drop down under refresh press. Refresh all head back to optimal click and sell in 11. And you should see your optimal aina so we can go ahead in this macro now or this recorder . So now we're gonna head into developer, and I go back into that module one. I want to take the sub optimal will copy it. Head back to this workbook. Go to the end of the last, um, macro that we ran, which was sort of one again. You could delete these out if you want to. Don't necessarily have to. Another thing that we can do now is go ahead and right click on this module one click removed. Module one and no, you don't need to export it. So we're gonna go and head back to this year and we're gonna insert or last text box. So I'm going to type in up the mole move to the middle size 16 Far bold, going to do the format shape again. Um, I guess I'll put it as purple. Doesn't really matter. I'm gonna sign the macro, and it's gonna be this workbook optimal. As you can see, a bunch of my a bunch of the ones that you saw earlier gone. It's because I closed the my other workbook that had open. So I select. Okay, so now this file is pretty much complete. I'll give you a little bit of a run through of it now. So the way it would work is you would end. Put all of your data into the input tab. So you would get this from off somewhere online. Go to your filter tab. You have all this set up already. You can click the drop downs to filter out teams. And what not? Of course you're gonna press input. Then you end up pressing filter because I didn't have any teams. Blanket didn't filter anyone out, which is fine. Then I will say transfer. So then transfer still leaves me on the filter taps. I need to head over to the optimal tap. The only reason I left it on the filter tab was because I wanted to make sure that everything air that you see that the player slash team has been filtered out. So then you head over to the optimal tap you pressure sorter button, which is sorting by your bang for the buck. Last thing you do is press your optimal button again. This thing pops up solver found a solution. So that's great. You press OK and everyone is updated automatically for you. It was a pleasure. Please save the file. It was a pleasure doing this project, and I hope that you found it useful. If you have any questions, please feel free to email me at J. M. Williams at Gmail Arms. R J M. Williams. I'll type it up, Lloyd J. And there it is. Thank you. Once again. 12. Formulas: still had a little more set up that needed to be done. I needed to add players, assuming that you could only have eight players salary of 50,000. Then I had to add in your quarterback two running backs. Why receivers, tight ends and, um, your defense. Last special teams. We're going to use this later. We'll link it to a pivot tab, and this will display your optimal lineup. As you can see up here, added a few equations and these were going to be very important to the optimization problem with the budget constraint of 50,000. So please copy these formulas in exactly the way that you see them. This ratio right here is your bang for your buck ratio. So it's essentially saying, basically, if a two is blank, then say zero make the ratio equal to zero. Same thing goes for D. Any of either one of those are blank. Go ahead, just make it zero. If not, you're gonna take your projection and divide it by yourself or you to get your bang for your buck. So again, please copy it in exactly the way you see it up here for your rank. go ahead. Just type in zero thes. They're gonna be binary, so eventually you'll see it is equal to you zero or one in your in your Q B section. What we're looking to do is we're looking to match up this Q B and H one to a position over here and column C. So we're essentially saying, if column C shows qg than take G two, which is your ring and multiply it by one. If it's not equal to that than just go ahead and make zero. So please pay attention to the dollar signs is they're very important. Dollar science and formulas hold things constant, which essentially says that you can't change this cell number or the cell itself. So play attention to where the dollar signs actually are. Because we are gonna end up copying this down. So our b you look at this formula here and police type it in exactly the way that you see it. I'll give you a second with that. All right. Moving on to a wide receiver, go ahead and copy the formula once again that the exactly the way that you see it up here again with tight end do the same thing exactly the way you see it up here. Um, again, I can't stress how important these dollar signs are when we copy things down. So we'll do the same with the defense last special teams as well. So please make sure that you copy the formula. Now what we're going to do is we're going to copy from H two. I will highlight from H two to L two and wound up dragging this down 200 or down to 201 And that's because when you set up the solver equation, which is ah, on optimization problem with the budget constraint of 50,000 in this, it's only gonna look at your top 200 players. And that's actually OK, because it's not like you really want second string players or third string players in your lineups anyways, So if you look at your top 200 players, you should be OK, So we're gonna go and drag this down to 201 Yes, I think that's to a one will get good head back up. We're gonna just copy this zero and we'll drag it down to 201 as well, and the reason that a copy the zero separately is because if I didn't do that, then it would end up changing them toe. 1234 and so on. So, actually, in the copy paste special values this one, uh, now we go to the ratio, and we're actually gonna drag this one way down here to drag it down to a 1000 and one or something like that. You could drag it down just past 1000 again. This allows you to have more than 1000 players or roughly 1000 players within your equations, So we'll copy it all the way down. I'm just going to stop it at 105 Think that's fair enough? We're gonna pace that one instead. Pay special values now had escaped, and I'll click on a two again 13. Sorter: Now we're ready to record a sorting macro. We're gonna end up sorting on a ratio. Um, this is our bang for the buck ratio. Basically, I've said every salary equal to 8000. You don't. You shouldn't do this. And you don't have to do this. The salaries will come from the input tab. I only said it this way. Just Teoh demonstrate. But remember, don't don't change the salaries or anything like that. It should come directly from of your Google search earlier on. So what we're going to do is we'll press the record button. Let's go ahead and just name it disorder one. Okay, so then we'll go from a one to f one and then we'll drag this all the way down, and I think I set it up. We'll we'll drag it down to the end of the formula. I happened to end our form a ratio formula at 1000 and five. So on the ribbon will click data. We'll click sort and the drop down you'll select ratio. And in this drop down, you want it to be from largest to smallest. So then you press OK, and as you can see here. It looks like Antonio Brown gives us the most bang for the buck. So that's a good thing. It looks like all of these names are realistic here. So then what we're going to do is, well, uh, we'll end our macro. We're going to do that inserting of the text box once again. So insert text box type in disorder. And again, I like to move mine to the middle and use around fought 16 and bold it. I'm going to, right Click it Teoh format that shape. And this time I will change the color of it to be some sort of blue I now need to go into developer under that module. One take this order and finally end of the last macro that we completed and paste it into the last macro that would completed was transfer. So do not face it within transfer, find the end of the sub and then click enter a few times. So we create a new subroutine again. I don't care to have these little apostrophes in there, so I remove them. You don't have to. So click on reset and back to Excel. We're going, Teoh, assign the macro and this will be this workbook sorter one 14. Solver: we never did to set up our solver equation. So in order to do that on your ribbon, click on data and out to the far right, you should either sees something that says Silver or a question work with an arrow pointing to the right. If you hover over that, you should see Salter. So go ahead and click on it. So we need to set the objective. The objective here is to maximize projected points, so we're going to go and click and P two and the variables that we're going to change our the rank. So we're going to scroll all the way down to a one. So now we need to set some constraints, so we'll add one. We need our salary to be less than the given budget, or less than or equal to are given budget. So will have p one being less than or equal to no one. We'll click add. We need our players to be equal to eight. So p three with equal 03 click add same idea goes for each position. I hope that you're following along with this were simply setting the P equal to the O. So I received her be equal to three tied in the equal to one and defense and special teams will be little one as well. It's one more that we need to add and that's making these binary. So you're gonna highlight G all way down to two. A one. Sorry, I school a little too far, but there we go. So says B i n here. We're gonna that in there Press. OK, so this is what your solver parameter should look like. Um, make sure that this does say simplex LP it may say gr nonlinear. So go and change it to simplex lp This check Marcus find for make unconstrained variables not negative. That's that's good to keep in there. So then go ahead, press solved. And as you can see, this is good news. It's a solver. Found a solution. All constraints and Optima ality conditions were satisfied. It's good press, OK? And what you'll see here is each player's Phil. Then I reached my budget and this is my projected total over here. You'll see that the players that it wants me to choose have one's next to them. So Antonio Brown, it wants me to pick him. It wants me to pick Cam Newton. Um, wants me to pick Julio Jones again. I didn't mess around with the salaries. Um, so don't touch this hour. Just make sure whatever comes out of the your Google searches what you put in there. Um, let's see. It wants me to pick up. Gronkowski is my tight end, so that worked out just fine.