TDawg's Madden 17 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 every 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:

Attachment 129401

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.

Attachment 129084

-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:

Attachment 129084

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:

Attachment 128927

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:

Attachment 128228

-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 up****** 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.

[Old Man Nathy]

Nice work. So is the main purpose to help you choose between two similar players on a depth chart if you are either interested in their potential or you have a specific play style (or scheme) that puts a higher emphasis on different attributes (e.g. you like playing with a QB with good mobility but don't care about awareness as you sim most games)?

[tdawg3782]

Quote: Originally Posted by Old Man Nathy

Nice work. So is the main purpose to help you choose between two similar players on a depth chart if you are either interested in their potential or you have a specific play style (or scheme) that puts a higher emphasis on different attributes (e.g. you like playing with a QB with good mobility but don't care about awareness as you sim most games)?

That pretty much sums it up. It's what Madden tries to give us with player scheme but way more specific.

[YeOldTonk]

Cool stuff. As I was inputting my OL I was thinking that this would be good for trades or FAs. Could just add your prospect on a line and see how he stacks up with the rest of your crew and see if its worth pulling the trigger.

Haven't tried it yet, but seems like it could be used for that.

Keep up the good work! Between you and OMN, I'm almost spending more time crunching spreadsheets than I am playing football games!

[RogueHominid]

Really cool stuff, TDawg!

[tdawg3782]

Quote: Originally Posted by YeOldTonk

Cool stuff. As I was inputting my OL I was thinking that this would be good for trades or FAs. Could just add your prospect on a line and see how he stacks up with the rest of your crew and see if its worth pulling the trigger. Haven't tried it yet, but seems like it could be used for that. Keep up the good work! Between you and OMN, I'm almost spending more time crunching spreadsheets than I am playing football games!

Yep. That is one of the main things I use it for too. Really helps to see how guys would fit on your roster.

[burth179]

Gonna try this out thanks man. Should help me especially in my on-line franchise. My off-line stuff is pretty straight forward at this point, but will come in to use soon enough (I did a "expansion team" where my team is basically all scrubs except for my first draft and a few decent veterans).

[YeOldTonk]

Love this for after draft off season.
Can see how your draftees and FAs fit into your system.