TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

[tdawg3782]

VERSION 2.0 IS UP (12/7/17)

V2.0 is now available (12/7/17) Details below:

It's been awhile guys. I've been working and tweaking this tool ever since Madden 18 came out and finally decided I'd share what I have come up with. IMO it's a cool little companion to make your CFM have more lasting power and a way to really develop your team in the way you see fit. With that on to the new features:

No More Macros
This was a huge step in the right direction. I love Macros but it was a hassle to redo your depth chart every 4 weeks (or whenever you progressed your guys). Now when ever you change something in the Roster Tab it will automatically change in the correct Depth Chart Tab on the fly. I'm not sure why I didn't think of this before but there it is lol. So if progressing your QB you change his Awr from 80 to 84 in the QB Depth Chart Tab it will change automatically for you.

Position Specific Attribute Percentages Located on Roster Tab
I've put the percentages you use to calculate your personal Ovr for each position on the roster tab. This is just an easy way to see what values you covet most when upgrading your players. Anything you change in each Position Tab will automatically change on the Roster Tab. No need to have to change both.

Ratings Formula Rework for Madden 18
I've tweaked the formula to go more with Madden 18 (not a huge difference from 17) but also was much more thorough this time then before. This made the calculations even more precise.

Ovr and Pot Difference Rows
This is located on each Tab after the regular Ovr and Pot. It's just a quick look at how the players stack up with one another. For example it will quickly show the difference in Ovr and Pot from the starter and the 2nd string guy, from the 2nd string guy to the 3rd string guy, etc. If the guy below is withing 2 or less as the guy above him it will highlight pink to let you know they are very close in Ovr. With Pot if the guy below has a higher Pot then the guy above it will highlight pink. Again just a quick way to tell how close some of your backups are to over taking the starting spots and so forth.

Team Ratings Reworked
This was just a quick rework to coincide with the Ratings Formula Rework. Nothing to crazy here. Just some of the Ovr were lowered. Again this is just a quick visual guide based on my formulas and is not exact science.

Salaries Tab
I've included a Salaries Tab mainly because last year I went into cap hell a few seasons in because I didn't have a big picture way of keeping track of my contracts. Once you input all information you will have a lot on information at a glance such as: total salaries for the next 5 years, number of players under contract for the next 5 years, average salaries per player for the next 5 years, current salary cap, how much you are over/under the cap for the next 5 years (the last 4 years are an estimate because you don't know the exact cap number), and how much your average salaries can be to hit your cap and have a full 53 man roster. You will also be able to sort your players in many ways. Each year all you have to do is click on the "Sub 1 from Years Rem" button and it will automatically take one away from years remaining. Then select range F1 to I69 and paste to E1. Last step is to change I1 to the next year and input any data that needs to go into the year. One other thing is the 3 contract templates to the right. One for 5, 4, and 3 years. Simply plug in the total contract value in the correct years offered and it will break the contract down for you (would be nice if Madden did this). Now by no means is this a super accurate way to see how much you will be paying when you sign a new guy to a contract but it will give you a general idea so you can plan ahead better.

QB Sense Pressure Trait
Anybody who has done a lot of simming as I have (for my XP Sliders) knows that there is an issue down the road with to many QBs having the Ideal sense pressure trait. This causes issues with sack totals big time. We're talking league leaders having 10 sacks. So this chart is something I've created based off of JoshC1977's chart he has implemented in his slider thread. Basically it will rate a QBs sense pressure trait off of his Awr rating. It looks like this: Awr<70=Paranoid, Awr between 70-79=Trigger Happy, Awr is between 80-89=Average, and 90+=Ideal. So simply plug in the data and it will spit out the corresponding trait you need to change it to. This is an optional thing to do and is by no means necessary but I think it adds more to my CFM and I enjoy it.

Notes and Future Stuff
One note is that I don't have this set up to put OLB at DE and DE at OLB for 4-3 defenses. I hadn't planed on releasing this again this year and since I only use 3-4 defenses I didn't bother putting in the option. I believe its doable but I'm not in a hurry currently. If you'd like to do it with this version just plug in you OLB player data in the DE section of the roster tab and vice versa.

I don't know about future ideas. I don't have any currently. It's more of me playing, seeing a need, and creating from that idea. I'm always open to suggestions though and welcome any and all.

I plan on making a quick video tutorial using this to make it as easy to understand as possible. Hope you guys enjoy.

V1.4 is now available.(12/20/16) Details below:

Spoiler

Okay guys so here’s v1.4. I’ve done quite a bit of work on this from the last version as you can see just by the file size. It went from 220kb for v1.3 to 1.66mb for v1.4. So without further ado let me tell you what I’ve done

-Complete rework of my ratings formula

Before the way I made the formula was to use the base Madden roster, go through every position, and write the high and low for each attribute used per position. I then calculated a high/low Ovr. I then ran this through an adjusted rating table based on the high/low and had it spread out between 99-40. This worked quite well as long as you kept your weighted percentages balanced. What I noticed is when you go pretty heavy on certain attributes and light on the others it skewed the Ovr either high or low. So it had to be adjusted.

What I’ve done is based on what I did before. Grab the high/lows for each important attribute for each position. But instead of getting a Ovr from that I created a separate ratings table for every single important attribute for every single position on a scale from 40-99. What this did was really create separation between players even if the actual rating didn’t seem to differ too much. For example, the highest Speed rating for a WR is 96 and the lowest is 78. That is only an 18-point separation from best to worst. So I spread this out to a 69-point separation. That means a guy who has 96 or greater would have an adjusted rating of 99 and a guy with 78 or less would have an adjusted rating of 40. A 92 rating gets you an adjusted rating of 85 and so on. I did this for every single rating for each position. From those adjusted ratings I then calculated a new Ovr. Here is the ratings scale:



Everything is so much improved now. Really like where it’s at.

-New highlight rules

Before I set up the rating highlights mostly how Madden does it except on a narrowed down scale of 99-90, 89-80, etc. But that doesn’t make much sense in reality. Back to the WR Speed rating we talked about earlier. The scale was from 96-78. That means most all WRs in game would have a green or yellow highlight with just a couple having an orange and none having red. Well how do you tell the top guys from the scrubs? I used a percentage based off the adjusted ratings scale. So 75%-100% is green, 50%-74% is yellow, 25%-49% is orange, and 0-24% is red. This will give you a real idea of how this player matches up on your team with a quick color coded presentation. I also did this for each attribute, at each position because they all had different ranges to work from. But it works great.

-Macros have been updated to include 4-3

There are now two depth chart position buttons. One for 3-4 and one for 4-3. The 3-4 does exactly what it did before. DT/DE are imported on all DL tabs and OLB/MLB on all LB tabs. 4-3 will put DE on the DT tab and OLB tab while also putting OLB on the DE tab. One thing I wasn’t able to add because it would have caused lots of code to be rewritten was making DT go to the DE tabs and MLB going to the OLB tabs. If you are running a 4-3 and want those positions to go to those tabs just go to the Roster tab, copy all player info in MLB position group and paste to OLB position group. Still keep them in the MLB position group as well. For DT copy all player info in that position group and paste to the DE position group. Again still leave the info in the DT position group as well.



-Formatting adjusted for a more pleasing view (IMO)

Made every group a table with formatting to give things a cleaner look. At least to me. Not much here but I think it looks good.

v1.3 update info below (12/12)

Spoiler

Pretty big update here guys. So what I've wanted to do for awhile is have a main roster page where you input all the info for your players. Then at the click of a button your Depth Chart Position tabs would be populated with all players from your Roster tab. This is now available. Not only will your players go where they are supposed to but also players that can play other position will go there as well, with all their attributes going into the correct spots even though they are different from the Roster tab. When progressing your players just edit in the Roster tab, click Populate Depth Charts, and boom it's all updated.

The other thing I added was a button which adds 1 to all the players age's in the Roster tab. This is just an easy way to age your players each season. There is also subtract 1 button if you make a mistake. The thing with macros is there isn't an undo button for them so this is that. Here is a pic preview below:



Key features at a glance:

-One Roster tab where you will in all data on all your players (still grouped by position or position group).

-Macro written to take players from Roster tab and populate the Position Specific Depth Chart tabs including positions that can play other positions (ex TE/FB, DE/DT, etc)

-Macro written to add 1 to players ages to simplify aging your players each offseason.

-Macro written to subtract 1 from players ages just in case you mess up with the add 1 button. Essentially an undo button.

-Plenty of room to add players on the Roster tab for each position.

One note is the DC column will get overridden to whatever shows in the Roster tab when you populate. There was nothing I could do about that.

If there are any questions feel free to ask. My next task is the opponent scouting/gameplan feature. Thanks again guys for showing interest.

v1.2 update info below (12/9)

Spoiler

-I've added a team ratings tab. It uses a letter grade system to give you an overview on how good your starters are and also how much depth you have. It takes all the info from each position tab so be sure to sort them by how your depth chart is set. I've also allowed you to select either a "3-4 D" or a "4-3 D" which will take into account how many MLB's and DT's start, depending on scheme, and give you an accurate letter grade. Pic below:



One other note. As far as the depth rating I mostly went 2 deep on the depth chart after the starter and averaged their ratings to give you your letter grade. The only one I didn't do this on is QB and FB where I just went with the backup. That's it.

I am working on how to make this a weekly scouting tool for the team you are up against so you can really gameplan against the other teams weaknesses. That's my next update and it will be pretty big I think so it may take awhile. Thanks guys. Hope you enjoy.

v1.1 update info below (11/22)

Spoiler

Made a few minor tweaks. Pic below:



-I added a "DC" column which stands for Depth Chart Position. Just a way you can organize your players differently. I use 1, 2, 3, etc for depth position, Inj for injured, and PS for practice squad players.

-Used Data Validation for the Dev column so there is no way to miss type it.

That's it for now. Again any suggestions are welcome. Thanks.

v1.0 Info below (11/18)

Spoiler

Hey guys. So I made a very similar tool like this last year for Madden 16. I recently had some motivation and got around to updating it for Madden 17. Here is a screen shot:

It's very similar to what I did last year. The basic premise is you plug in your players and their attributes. You then put a weight % on how much you value each attribute (up to 100%). My formulas will then crank out an Ovr that is unique to how you want your team built. What Madden tries to do with scheme but way more individualized.

My Ovr is not a Madden Ovr by any means. It's on a 40-99 scale with those being extreme extremes. A player that gets above 90 is a beast and anyone below 60 is garbage.

A couple new things I've added this year are a Potential Ovr based on Age and Dev modifiers, reorganized attributes for each position to make inputting as easy as possible, and some color formatting to make comparing players pop out easier. I'll get into those a bit below.

Potential Ovr: What I did here was add a formula to gauge potential Ovr for all players based on age and dev trait. Now this isn't exact science so the potential Ovr given may be off from what they can actually achieve but it's a best case scenario calculation. Meaning if you give that player tons of playing time, he hits his goals, and he gets some training (either gameplanning or focus or both) they could hit that Ovr. It is also a good way to gauge who you may want to start if two players have similar current Ovr.

Age and Dev Modifiers: I based the age modifier on the 26 year old rule. Where players don't have much opportunity to progress much after this age because the cost of upgrading attributes has gone up exponentially. So you'll notice anyone 26 and over will not have anymore potential Ovr expected. They are pretty much maxed out. This helps in deciding when to let players go and also for Free Agency. The dev modifier is just a simple add on. The fourmla looks like this:

(26-age)*(dev)+background current ovr

This then gets run through my ratings formula and gives the Potential Ovr.

Currently I have the dev modifiers set as this:

Slow: .75
Normal: 1.5
Quick: 2.25
Superstar: 3

I played with these numbers for awhile and decided this made the most sense for me. Again don't take the potential Ovr to be exact. It's more for a gauge on who to start/keep/release/etc.

Reorganized Attributes: All I did here was set it up in the order it's listed when you click on the player card. Tab over to Skills and just plug them in. If you are good with the number pad it goes super fast. QB, OL, and DL all have Stamina listed at the end. I didn't include that attribute so just skip it.

Color Formatting: What I did here is kinda like what Madden already does on it's player card (Green, Yellow, Orange, and Red). But it makes comparing all your players so much easier because you can see all the players on one screen. The color key is as follows:

>89=Green
80-89=Yellow
70-79=Orange
<70=Red

Superstar=Green
Quick=Yellow
Normal=Orange
Slow=Red

A few other notes: All sheets are protected to preserve formulas. There is no password so they can be unprotected whenever you feel like it.

Last thing: The main reason for this tool is to be able to have a complete picture of your roster and to be able to really fine tune your scheme and make sure you get the best fit for your team. I hope you guys enjoy this and it makes your Madden experience better. Also I am all about adding to this so if anybody has a really good idea let me know and maybe I can implement it (if it's not crazy hard lol). Thanks again.

[tdawg3782]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

Wanted to start a new thread because the old one said Madden 17 and this is for Madden 18.

[tdawg3782]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

Not sure how many are using this or have interest but I had an idea and need input. Last year there was a suggestion to add some kind of weekly matchup tool to this. To compare your team to the opposing team and see where you can exploit a weakness by comparing say your LT to their RE and so forth. My question is what ratings would we weigh against each other? I can guess for sure based off general knowledge of how the game works but was just wondering if anybody had more insight on this? Thanks in advance.

[Joshduvernay]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

What's up T-Dawg,
Hey im on one of those silent guys who lurk around all of your guys post without ever saying anything but i figured its time to actually comunicate with the madden community. Lol. Anyways im going to download this tonight and try it out and il let you know what i think. Also did you consider possibly adding something so for contract suggestion?. For example a player with a certaing good rating at this position should get offerd at least a certain amount of years or money. Just curious. Since i usually try to build through draft i seem to always be way under salary cap anymore and i never try to lowball people unless its and oldrolder ffrege free agent on a 1 year prove it deal. Just a thought on making things more challenging. Thanks for all u do brother to try to make this game better. I swear u guys on here do more than ea itself. HA

[tdawg3782]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

What's up T-Dawg,
Hey im on one of those silent guys who lurk around all of your guys post without ever saying anything but i figured its time to actually comunicate with the madden community. Lol. Anyways im going to download this tonight and try it out and il let you know what i think. Also did you consider possibly adding something so for contract suggestion?. For example a player with a certaing good rating at this position should get offerd at least a certain amount of years or money. Just curious. Since i usually try to build through draft i seem to always be way under salary cap anymore and i never try to lowball people unless its and oldrolder ffrege free agent on a 1 year prove it deal. Just a thought on making things more challenging. Thanks for all u do brother to try to make this game better. I swear u guys on here do more than ea itself. HA

I haven't. My strategy usually when resigning is to offer the initial contract. If denied I will up both base salary and signing bonus by 5 clicks. But I do know Armor & Sword had an awesome method of resigning guys that should still be in his slider thread. At some point I could put a tab in with his method if he'd be cool with it. Not super high priority though.

[Joshduvernay]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

Man i completely forgot about that. Ive been jumping around so many threads trying to get the game the way i like it it just slipped my mind. Ill check it out. Thanx

[RogueHominid]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

Hey TDawg!

This looks really cool, but I have a couple of dumb tech questions. I'm a Mac guy, so I had to DL an extractor to open the .rar file on my laptop. The xlsx file that I extracted appears to just have 3 tabs: one for team ratings, one for team OVRs, and one for QB traits.

Am I missing something? It sounds like I should be seeing more sheets within this file with other functions, but I'm not.

Any thoughts?

[tdawg3782]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

Hey TDawg!

This looks really cool, but I have a couple of dumb tech questions. I'm a Mac guy, so I had to DL an extractor to open the .rar file on my laptop. The xlsx file that I extracted appears to just have 3 tabs: one for team ratings, one for team OVRs, and one for QB traits.

Am I missing something? It sounds like I should be seeing more sheets within this file with other functions, but I'm not.

Any thoughts?

There should be a little arrow pointing left that will move to more tabs. It looks like this:

roster tab help.jpg

[RogueHominid]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

There should be a little arrow pointing left that will move to more tabs. It looks like this:

[ATTACH]144060[/ATTACH]

Bwah! Thank you for being kind about that. Lo and behold, there IS a little arrow, and there ARE lots more tabs .

What an excellent tool. Thanks, man!

[tdawg3782]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

Bwah! Thank you for being kind about that. Lo and behold, there IS a little arrow, and there ARE lots more tabs .

What an excellent tool. Thanks, man!

No problem bud lol. We've all missed things like that at some point (or numerous times in my case lol).

Sent from my SM-G950U using Operation Sports mobile app

[The Chef]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

Nice tool man, thanks for sharing. I downloaded it and will be playing around with it today when I fire up Madden. I especially love the salary tab as I used to do something very similar so I could stay on top of my finances going forward and where to trim the fat so to speak. Again, thanks for sharing and it looks really good.

[rouk58]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

I was late to the party on this cycle of Madden and have not had much time to play, but I did have some time to take what you had worked with on your previous version and expand it some for my use. I added in some other positions, slot WR/CB, 1/3 tech DT/DE. I even added in returner. I also added a color coding feature that compared stats to the averages across the league for positions.

I think having impact blocking on the defensive players might be a good thing also, at least I read somewhere that this now means something for them. Weight might not be a bad thing, although I guess you can always edit that for your players and rationally explain the change in weight.

Having said that I like the new changes you made, I didn't take the time to see the new calcs you did, but am excited to dig into it.

As for the idea of match ups. The idea was sound at first, but it is certainly a very complex thing to try and capture. I had been giving some thought on how to achieve it, but came up blank. Things like a 1 tech DT vs your center. Comparing strength, block shed/run block, impact block, weight, and also pass rush vs pass block. The issue comes in, this same comparison would be needed against each of your guards vs that same 1 tech. Both of your guards and tackles would need to be looked at for a 3 tech and such.

Looking at the skill positions you could compare your TE/HB to LB/S, and WR to CB. But do you just compare MCV or do you include ZCV? I started getting lost in the weeds thinking about all of the combinations to examine. I can only imagine what it is like for an actual coach.

I hope this doesn't come off as being critical. I think the work you did here is awesome and I have really enjoyed learning how you made it work. I am looking forward to digging into your newest version.

[tdawg3782]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

I was late to the party on this cycle of Madden and have not had much time to play, but I did have some time to take what you had worked with on your previous version and expand it some for my use. I added in some other positions, slot WR/CB, 1/3 tech DT/DE. I even added in returner. I also added a color coding feature that compared stats to the averages across the league for positions.

I think having impact blocking on the defensive players might be a good thing also, at least I read somewhere that this now means something for them. Weight might not be a bad thing, although I guess you can always edit that for your players and rationally explain the change in weight.

Having said that I like the new changes you made, I didn't take the time to see the new calcs you did, but am excited to dig into it.

As for the idea of match ups. The idea was sound at first, but it is certainly a very complex thing to try and capture. I had been giving some thought on how to achieve it, but came up blank. Things like a 1 tech DT vs your center. Comparing strength, block shed/run block, impact block, weight, and also pass rush vs pass block. The issue comes in, this same comparison would be needed against each of your guards vs that same 1 tech. Both of your guards and tackles would need to be looked at for a 3 tech and such.

Looking at the skill positions you could compare your TE/HB to LB/S, and WR to CB. But do you just compare MCV or do you include ZCV? I started getting lost in the weeds thinking about all of the combinations to examine. I can only imagine what it is like for an actual coach.

I hope this doesn't come off as being critical. I think the work you did here is awesome and I have really enjoyed learning how you made it work. I am looking forward to digging into your newest version.

This is exactly what was happening when I was trying to come up with a method for the weekly gameplan idea. I started overwhelming myself lol. Still want to try something in the future just not there yet. The way my mind works is all of a sudden it'll just come to me when I'm not even thinking about it. When I try to sit down and do it I just draw a blank.

Anyways I am working on a new feature. I do that know how useful overall it would be to everyone but I'll be using it for sure. It's basically a progression assistant tool. I'll get more in depth in another post but basically it takes how much the upgrade costs, how much weight you have on that particular attribute, and gives you a cost value to see what the best deal is. It's working quite well so far. Still got to do a few more positions though. Again not for everyone but a cool little add on feature.

Sent from my SM-G950U using Tapatalk

[tdawg3782]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

I think having impact blocking on the defensive players might be a good thing also, at least I read somewhere that this now means something for them. Weight might not be a bad thing, although I guess you can always edit that for your players and rationally explain the change in weight.

Having said that I like the new changes you made, I didn't take the time to see the new calcs you did, but am excited to dig into it.

Forgot to reply to these two topics. I agree to a point on the impact blocking. It definitely does affect the defense this year as opposed to other years. However I really don't know the extent on it and just playing the game I haven't really noticed anything drastic compared to players who have a low or high IBK rating. Something I'd like to implement at a later time but not a priority as of now. Good call though.

The main difference in the calculations this year was the amount of data I was able to use. Last year I basically took the top rating and bottom rating for each attribute at each position. It worked out nicely but still didn't account for outliers at the top and bottom. This year with the use of the Madden Companion app I was able to export the entire roster into excel and run full calculations to get a solid average instead of an estimated average. I also didn't put caps on this year. For example if the top QB speed was 92 that would be a 100% value. Anything higher, while rare, would still result in a 100% value. So anything between 92-99 would result in the same value. Now if there is an outlier somewhere he will get credit for it and his rating will reflect that little extra. Again not a huge deal but much more precise all around.

Lastly any information I get from you guys whether it be an idea, constructive criticism, or whatever is cool by me. Sometimes I read something and then an idea just pops in my head so it's all good.

[bravesfan1984]

Re: TDawg's Madden 18 CFM Roster/Depth Chart Tool(Excel required)

TDawg, just wanted to chime in and say thanks for this file! I'm getting ready to finally begin my serious Cowboys CFM and have input my entire roster. This is quite the handy tool to analyze your roster.