Page 1 of 2 12 LastLast
Results 1 to 10 of 14
Like Tree7Likes

Thread: Tactical Analyst 1.0 - Excel tool

  1. #1
    Apprentice Jean.Monico's Avatar
    Join Date
    Jul 2017
    Location
    Porto Alegre, Brazil
    Posts
    170

    Tactical Analyst 1.0 - Excel tool

    Hello dudes,
    Ready to start breaking the system?!

    I would like to share a tool, which I developed to help me choosing which formation to use against my opponents.
    This spreadsheet was much bigger. It is smaller and simpler now. And the reason is I spent a lot of time trying to add features like the instructions (mentality, passing, counter-attacks, tacking style, etc.), but the calculations started getting really insane (loads and loads of variables), it had not a secure parameter basis and I forgot the main reason I started developing it: I just wanted a tool better than the “Counter-formation table”. I talked to Toxicatl and he reminded me of that.
    Then, the only instruction I left is the “Focus Passing”.
    It looks like it now:

    Tactical Analyst 1.0 - Excel tool-01.png

    I would like to emphasize the basis I created it.
    And it will be open for you to make your own adjusts to fit what you believe it is right.

    WARNING: I am only a level 6 manager. I have been trying to observe many patterns from matches, especially on Associations matches. I made many changes on excel data to fit those match numbers. It looks fine for me, but my “experience” is limited to it. You will be able to make the adjusts and have your own personalized parameters ☺

    How do I fill the excel spreadsheet?

    It is simple. I used a grid on excel to match Top11 grid. You go to the spot you want to place a player and write his position name on it. Use the keys + and – to indicate arrows up and arrows down, respectively. Let’s build a 41212ND? Follow the guide below.

    Tactical Analyst 1.0 - Excel tool-04.png

    Of course, you can make some sort of sheee1eeet, like adding a DC on ST place. Do not do that or you will mess with the calculations!

    How did I calculate POSSESSION?

    The possession is based on “who has more players close to the midfield line”. It means that formations like 352-flat, 451-flat would have some advantage on possession. If you, for example, put arrows up to your DL and DR, their positioning will contribute a little bit more to the possession. The same happens if you put arrows down to your STs. The closer to the midfield line, the more you have possession.

    You don’t agree with it? Me too!
    I experimented creating a biiiiiiiiiiiig equation, including passing and tackling style, pressure, counter attack. It looked like a real football simulation and I should sell it to real clubs (or even to top11 devs hahaha).
    Then I played like 20 matches to check what would happen here in Top11... And it just looked like I wasted my time.

    If you do not believe that Top11’s possession is based on “who has more players close to the midfield line”, you can try a 3221-butterfly with arrows up on DML and DMR, arrows down on AML and AMR (have fun and put middle DC with arrow up too), short pass, flanks, high pressure and hard tackling. Your possession will be rock solid. I do not agree with it, but it is how it is in the game.
    (Note that short/long pass, low/high pressure, mentality, etc. are not included here)

    How did I calculate chances of ATTACKING vs. DEFENSE?

    The chances are based on the strengths of the 3 zones: left flank, middle and right flank. The more players you have in any of these sectors, the more strength you have. Let us use the example of 451V vs 343 classic (I will not include arrows here).

    Tactical Analyst 1.0 - Excel tool-02.png

    Team A has the following:
    - Left flank: DL + AML + “a little bit”* of a DC and MC
    - Middle: DC + DC + DMC + MC + MC + ST
    - Right flank: DR + AMR + “a little bit”* of a DC and MC

    Team B has the following:
    - Right flank: MR + “a little bit”* of DC, MC and ST
    - Middle: DC + DC + DC + MC + MC + ST + ST + ST
    - Left flank: ML + “a little bit”* of DC, MC and ST

    I assumed that depending on the positioning of a player it would contribute as:
    - “D” line: 100% defense / 0% attack
    - “DM” line: 75% defense / 25% attack
    - “M” line: 50% defense / 50% attack
    - “AM” line: 25% defense / 75% attack
    - “ST” line: 0% defense / 100% attack

    If you make some calculations based on it, you would see that 451V would destroy 343 down flanks. 343 would only have a small chance trying through the middle.
    Arrows would make some sorts of change

    *And later you will understand what would be this “a little bit”.

    How ARROWS work?

    The team has, basically, 2 formations: 1 formation when it has the ball (say attacking formation) and another formation when it hasn’t the ball (say defending formation).
    Let us say you play a 442 classic. You add arrows up to DL, DR, ML and MR and arrow down to 1 of your ST. Your 2 formations will be like:

    Tactical Analyst 1.0 - Excel tool-03.png

    (I used as source what was in Championship Manager 4 – I bet it’s the same here in Top11 ;p)

    What is not included?

    There are a lot of factors that may make difference and are not included in this tool. The purpose of the tool is to help you finding a formation which has more chances of winning. It does not guarantee you will win.

    Not included:
    - Luck, roll dice;
    - “Crisis”;
    - Team quality difference;
    - Match preparation;
    - Team “Chemistry;
    - Instructions;
    - Etc.

    How do I personalize the data parameters?

    The 2nd sheet is called “Parameters”.

    Tactical Analyst 1.0 - Excel tool-07.png

    Get in it and edit the 2 yellow tables: Focus Passing contributions and Attacking/Defending contribution.

    Here, in focus passing, I mean, for example that a DR contributes 100% to the right flank, and 0% to left and center sectors. You can edit it and put 0, 0.2 and 0.8 for example. It will not be a huge difference at the results, as it will change everything for TEAM A and TEAM B equally.

    Tactical Analyst 1.0 - Excel tool-05.png

    Here, in Atk/Def contribution, I mean a MR contributes 50% to defense and 50% to attack when team is without ball (defending formation). Same MR contributes 40% to defense and 60% to attack when team has the ball (attacking formation). See the difference on an “MR+” ^^. Feel free to edit this data too. Again, the changes will be the same to both teams A and B.

    Tactical Analyst 1.0 - Excel tool-06.png


    Suggestions? Questions?

    Hope it gives you some help!
    If you have theories about how the engine works, let me know and I will try to make the appropriate changes for next versions.
    Last edited by Jean.Monico; 11-07-2017 at 02:09 PM.
    Anarcho and brolly like this.

  2. #2
    Apprentice Jean.Monico's Avatar
    Join Date
    Jul 2017
    Location
    Porto Alegre, Brazil
    Posts
    170
    Forum is blocking me from uploading the file ;O

  3. #3
    Apprentice Jean.Monico's Avatar
    Join Date
    Jul 2017
    Location
    Porto Alegre, Brazil
    Posts
    170
    Oki. The file is here.
    Attached Files Attached Files
    Anarcho likes this.

  4. #4
    Famous Toxcatl's Avatar
    Join Date
    Oct 2016
    Location
    Chile
    Posts
    1,149
    All this stuff is highly speculative.
    I'm testing it!
    It wasn't our day.

  5. #5
    Newbie
    Join Date
    Mar 2017
    Posts
    6
    Tactical Analyst 1.0 - Excel tool-1.png
    Tactical Analyst 1.0 - Excel tool-2.png
    I found there are a little bugs on your tools.See~
    Jean.Monico likes this.

  6. #6
    Apprentice Jean.Monico's Avatar
    Join Date
    Jul 2017
    Location
    Porto Alegre, Brazil
    Posts
    170
    Quote Originally Posted by gemini013 View Post
    I found there are a little bugs on your tools.See~
    Oh ohw. It's not a bug. It's me messing an equation hehehe Thanks for reporting me this.

    Between a lot of changes and saves, I had a "limit of 100%" on these calculations. I thought I removed this limit on all formulas, but I forgot it on Team A through the middle.

    Tactical Analyst 1.0 - Excel tool-sem-t%C3%ADtulo.png

    Now it's fine and updated here. If you find another like that one, let me know, please!

    Thank you again, gemini.
    Attached Files Attached Files

  7. #7
    Dreamer
    Join Date
    Dec 2013
    Posts
    329
    And now it is changed..

  8. #8
    Apprentice Jean.Monico's Avatar
    Join Date
    Jul 2017
    Location
    Porto Alegre, Brazil
    Posts
    170
    Quote Originally Posted by Pangihutan Manik View Post
    And now it is changed..
    No no no. I saved the file with some arrows on 451V, so it changed the overall results. If you remove them, it comes back to 130% x 82%

  9. #9
    Dreamer
    Join Date
    Dec 2013
    Posts
    329
    Quote Originally Posted by Jean.Monico View Post
    No no no. I saved the file with some arrows on 451V, so it changed the overall results. If you remove them, it comes back to 130% x 82%
    I mean with this new update, no arrows and no dml/dmr. So, it will be different result. By the way, this formation layout has been used by the 1st CM, right?

  10. #10
    Newbie
    Join Date
    Mar 2015
    Posts
    48
    I hope Jean will have new version compliance with TE 2018 soon.
    The hypothesis is not who get more players to mid line anymore.
    Jean.Monico likes this.

Page 1 of 2 12 LastLast