Power training tool

[Kwal]

Hello all you top eleven managers,

I want to share my Excel file which I use to train my players (mainly fast trainers) with you guys. The project is still in development and although it works properly, it might not cover all the cases (it is not completely tested yet). If you find any invalid output please feel free to share it with me so that I can adjust the file. I’m aware that the amount for the sets of drills and the green packs might be off since it is static and only works for specific ages.

It looks like this:


Download it from here:
T11 power training tool v.20200623

What can you do with this file?
You can mess around with the order of the drills to see what the estimated end result will be for your new champion. This way you can find the best suitable order.
This is my current team and its seasons stas build with the help of this file. The keeper is bought through recommended players or the scout, as I believe it is a waste to powertrain a GK. For a better picture quality click here.



Sounds and looks nice, but how does it work?

1. You'll only need to fill in the fields with a BOLD border. The white fields are mandatory (except for the focus skill) and the green are optional.
2. First fill in your players age, position and his current skills (upper white fields)
3. Then select your preferred drill order on the left of the table (the upper drill will be trained first). Too help you pick a good drill order you can make use of the “ASSIST HEADER” on the right side of the table. Here you can see the ratio of white skills trained per drill accordingly to your chosen positions. The box in front of a drill will turn green once the training has been used in your preferred order.
4. The row "estimated result" indicates how your players will look once you are done pushing them around.
5. The “Green packs” column (light green) indicates how many green packs will be required. Caution: this service is not functioning properly at this moment.
6. The “Sets of drills” column (light orange) indicates how many sets will be required. Caution: this service is not functioning properly at this moment.
7. The “Skill points per condition” column (light blue) shows the effectiveness of the condition you spend. The higher the value, the more skill points you gain for spending your green packs

Awesome all this free info but I am a pro, I want to push it even further!
Okay champ! Once you’ve selected your preferred drill order you can adjust the maximum average % per drill in the light green column on the left. This way you can push specific skills even higher, but probably at a higher cost.

What are the planned improvements?

• Adding pre-sets per position to compare your drill order.
• Adding an automated player Tier calculator. This will be used to identify what kind of player you are training. Tier 1 will be the best fast trainer. (this will take me some time to figure out still, because I lack the data for it)
• Adding correct values for the skill points gained per condition. This will influence the estimation of the amount of green packs and drill sets (6x) needed.

A Tier calculator?! Sounds sweet, but how do you plan to do that? And will this mean that when I buy a new player I can use this sheet to check if he’s actually a fast trainer?
Yes it does! I am trying to make a tool which automatically checks what kind of player you are training. To do this I will need to figure out the formula behind the value of the players. I didn’t succeed in this yet.
Once I (or we) figured the formula, you will be able to check if the player you want to buy is in fact a fast trainer and his specific Tier rating.

Nice! How can I be of any help by improving this file?

1. I’m in need of the data for the max value (and 2nd best) for a player at every % and club level or the formula for the players value (if someone else could that figure that out for me would be awesome).
2. The second thing I need are the exact ratios of skill points gained per condition used. This is varies by a) the players current overall skill, b) age c) the Tier of the player.
3. Suggestions on improvements. Let me know what you miss and what you would like to see added to the file

How can I get your autograph?
You can’t. If you use this file and are satisfied with it, you can make a post just to let me know I’m not putting all this effort for nothing.

Important note:
There are already some nice sheets made by other managers like Arphaxad and Khris, make sure to check out theirs as well as you might find them better to use or more suitable for your needs. You can click their names to go their threads.

UPDATE 19-06-2020:

• Added a column with the average overall %
• Added a macro the automatically changes the name of the tab when you enter the player's name. Caution: it still gives errors on duplicates and empty boxes. I'll try to fix that in the next update.

UPDATE 23-06-2020:

• Added quick acces buttons to duplicate, clear and delete sheets
• Added a training efficiency column which indicates the amount of skill points gained per condition used. The higher the rating the more value you get for your green packs.
• Added a "focus skill" option to quickly see which drills contain the specific skill you want to train. This idea I took from Arphaxad.
• Added 2 columns behind the assist bar to see the average drill % before and after the power training. This might come in handy when you want to train your older players. Also an Idea from Arphaxad.

Known issues / bugs:

• When at the start of a dril the average drill % is exactly 20 / 40 / 60 / 80 etc. it stops counting the green packs and the sets of drills needed for that specific drill. It will be solved on the next update.

Discovered irregularities during data logging:

• a player could gain a (visible) skill point eventhough the trainingreport doesn't say a skill point was gained. Most likely the player will get that skill point on the next training.
• The randomness of the condition loss in training seems to also impact the individual skills on the selected drills. Therefor you might actually gain an extra point on a specific skill whilest the other skill will gain a point less.
• The Tier of a player might change at the start of a new season. I've seen a Tier 3 player at 18 become a Tier 2 at 19. There also seems to be a randomness factor here.

[khris]

Since the XP gaining is hidden, it's quite complex to calculate the exact gain of each player, because every player and even every training with same drill gain a different amount of XP...

The first TE versions showed that gaining, when we had no drills, there was a bar till 99, every completed bar added +1 skill point to the players... now a SP is a 4% (thats why the daily gift is a 4%) and... fast trainers usually could gain per training a progress of .. let's say 148xp, 162... then same training 132xp... so there's no exact formula telling anyone X condition used = always Y progress... this make things complicated to calculate and we only can know if a player is a fast trainer by training a SA or NP and using a hard drill....

Good job btw

[Kwal]

Since the XP gaining is hidden, it's quite complex to calculate the exact gain of each player, because every player and even every training with same drill gain a different amount of XP...

The first TE versions showed that gaining, when we had no drills, there was a bar till 99, every completed bar added +1 skill point to the players... now a SP is a 4% (thats why the daily gift is a 4%) and... fast trainers usually could gain per training a progress of .. let's say 148xp, 162... then same training 132xp... so there's no exact formula telling anyone X condition used = always Y progress... this make things complicated to calculate and we only can know if a player is a fast trainer by training a SA or NP and using a hard drill....

Good job btw

Thanks Khris. But you can identify a fast trainer by his value as well, which would make it an easier way to see. Testing a SA or NP is, according to my tests, influenced by the average % of the drill used. A max value player is always a fast trainer.
For the skillpoints gained per condition it is true that it is almost impossible to get exact values for this, But again you can get close by loggin the skill points gained for every different type of Tier player.

[RaiKo]

I think it's possible. You only need big data to build formulas.

[Kwal]

I don’t know why my main post is gone, but okay.

I have never played the first TE versions so I don’t really know how things worked there.
From what I understand we have different definition of a skill point. For me it’s when any skill gains a %. You are mentioning a SP being 4%? Anyway it is correct that you can’t get the exact formulas.

I believe that the problem isn’t the hidden xp, but that we only get to see the rounded numbers. Therefore it isn’t possible to get the exact formulas, but you can get very close by logging the progress of multiple players with the same Tier and age. Once you take the average from like 10 players you can get very close.
After we’ve got an almost accurate skill point (read 1%) gain per condition loss (CL) for all the circumstances (age, average drill %, tier) there is still the randomness of the CL per training. In Example: if you train sets of 6x Press The Play (base 18 CL), the CL for each training can differ from 15 until 21. This randomness will be impossible to tackle in a formula, but we should take the best average here. This can either be the base average of 18% or the average of like 10 players over multiple of trainings. I’m logging 2 players with PTP and the 1st has an average of 17,93% CL per 6xPTP over 28 trainings. The 2nd an average of 17,77% CL over 31 trainings. The small difference with the base CL of 18, will probably be narrowed down if we log more players.
Concluding: It will be impossible to get the exact formulas for these matters, but by using the semi-accurate averages we’ve logged we can get very close and it will give a pretty good estimation of the condition (green packs) needed to train a player.

About recognizing a Fast Trainer (FT):
Training a SA or NP to recognize a FT is possibly inaccurate. I’ve done some tests and found that the SA and NP are both influenced by the current average drill %. Therefor it might give a different result depending on the average drill % of the drill you are using.
If you know the Tier of a player, he will always be the same kind of player (they all train at the same speed). The Tier of a player can be known by the relation of his current value, his age, his average overall skill % and the club level.
According to my logs at a Club level 12 and a player of 55% average the values are the following:
Tier 1: 38.2M
Tier 2: 37.4M
Tier 3: 36.6M
Tier 4: 35.8M
As you can see the value difference is approximately 0.8M per Tier. Now we know that every Tier has the same ratio, we can use this in a formula. Once we figure out the Formula for the value of the Tier 1 players, we can easily get them for all the other Tiers.

[Arphaxad]

I don’t know why my main post is gone, but okay.

I have never played the first TE versions so I don’t really know how things worked there.
From what I understand we have different definition of a skill point. For me it’s when any skill gains a %. You are mentioning a SP being 4%? Anyway it is correct that you can’t get the exact formulas.

I believe that the problem isn’t the hidden xp, but that we only get to see the rounded numbers. Therefore it isn’t possible to get the exact formulas, but you can get very close by logging the progress of multiple players with the same Tier and age. Once you take the average from like 10 players you can get very close.
After we’ve got an almost accurate skill point (read 1%) gain per condition loss (CL) for all the circumstances (age, average drill %, tier) there is still the randomness of the CL per training. In Example: if you train sets of 6x Press The Play (base 18 CL), the CL for each training can differ from 15 until 21. This randomness will be impossible to tackle in a formula, but we should take the best average here. This can either be the base average of 18% or the average of like 10 players over multiple of trainings. I’m logging 2 players with PTP and the 1st has an average of 17,93% CL per 6xPTP over 28 trainings. The 2nd an average of 17,77% CL over 31 trainings. The small difference with the base CL of 18, will probably be narrowed down if we log more players.
Concluding: It will be impossible to get the exact formulas for these matters, but by using the semi-accurate averages we’ve logged we can get very close and it will give a pretty good estimation of the condition (green packs) needed to train a player.

About recognizing a Fast Trainer (FT):
Training a SA or NP to recognize a FT is possibly inaccurate. I’ve done some tests and found that the SA and NP are both influenced by the current average drill %. Therefor it might give a different result depending on the average drill % of the drill you are using.
If you know the Tier of a player, he will always be the same kind of player (they all train at the same speed). The Tier of a player can be known by the relation of his current value, his age, his average overall skill % and the club level.
According to my logs at a Club level 12 and a player of 55% average the values are the following:
Tier 1:38.2M
Tier 2: 37.4M
Tier 3:36.6M
Tier 4:35.8M
As you can see the value difference is approximately 0.8M per Tier. Now we know that every Tier has the same ratio, we can use this in a formula. Once we figure out the Formula for the value of the Tier 1 players, we can easily get them for all the other Tiers.

I would like to add that not only is the average drill level a determinant, but the drill level as well (# of whistles).

Skickat från min VOG-L29 via Tapatalk

[Rizla]

In first page you should add current level ex 80% and after training level ex 100%.
From the first glance i think your green calculations are about wright.
It's not that important to know how many greens excatly you are going to spend,
but is important to have a clue.
Nice work.

[Arphaxad]

Also thanks for the mention. I'll make sure to check your sheet out during the weekend. But it looks really sweet at first glance. I'll get back to you!

I'm also working on improving my calculator by adding power training. I hope to be able to finish it before the weekend ends as the new season begins. But I will mainly focus on the average drill level until we have sufficient data to determine the factors you've described.

[Kwal]

In first page you should add current level ex 80% and after training level ex 100%.
From the first glance i think your green calculations are about wright.
It's not that important to know how many greens excatly you are going to spend,
but is important to have a clue.
Nice work.

Thanks Rizla.

Do you mean the player's overall rating or the average rating of the specific drill (these are in front already)?
If you mean the overall average rating, would you want to see that per drill? or only the end result?

About the greens it will always stay an estimation. You can change the % gained per condtion loss in the tab "TABLE" yourselves if you find more accurate numbers.

[Kwal]

I would like to add that not only is the average drill level a determinant, but the drill level as well (# of whistles).

Thanks. I forgot about that totally. Everything now is based on the maxed out trainings. The influence of this I might add after everything else works properly.

Also thanks for the mention. I'll make sure to check your sheet out during the weekend. But it looks really sweet at first glance. I'll get back to you!

I'm also working on improving my calculator by adding power training. I hope to be able to finish it before the weekend ends as the new season begins. But I will mainly focus on the average drill level until we have sufficient data to determine the factors you've described.

You are welcome. And thanks, im curious to hear what you think about it.
Good luck with improving yours. If you have any questions or want any help feel free to ask.