Excel Attributes Spreadsheet (18/01/2009 03:21)

This is a three part blog: Introduction (this page), Setting Up Your Own Excel spreadsheet, and Using Your Spreadsheet.

The spreadsheet that I use to keep track of attribute gains is an Excel 2003 spreadsheet that looks like the following:



I think of it as five separate sections like the following:

          1               4

          2               5

          3

Section 1

Section 1 is the Player's data from the beginning of the season and will only need to be manually changed at seasonchange.

Section 2

Section 2 holds the last pasted values of the player's attributes. It is entitled End of Season as the final values will be the last paste done during the season. At the bottom of Section 2 is the average team Q which is the sum of all player's Q values divided by the number of players ... my squad has just passed the Q83 mark and is currently at 83.069.

Section 3

Section 3 is the manipulation section of the table. The data is pasted from ML into this section and then the players are sorted into positions and then alphabetical order. Spinner's invention of showing the Q added during the season (ie Q 83 (+2))  means that some macros are required to remove the parenthesis and parts therein. Once Section 3 is all sorted and ready to be copied to Section 2, some macros are run so that Section 5 will show the difference between the current values in Section 3 and the last values pasted in Section 2. After this Section 3 is selected and then copied and then pasted to Section 2 to replace the last pasted values.

Section 4

Section 4 is all formulae subtracting the values in Section 1 from the values in Section 2 to show the attribute gains during the season. The far right colums show the total amount of main attributes gained (the coloured cells) and then divides that by the total attribute gains. My last defender in the list, Simão Santos, has gained 19 attributes this season and 14 of them have been main attributes - therefore 74% of his gains have been main attributes.

Section 5

Section 5 is the difference between Section 3 and Section 2 before Section 3 is pasted to Section 2. Therefore it will show the player gains since the last time data was pasted into the spreadsheet. If you look closely you will see that my defender, Dylan Finn, gained +1 shooting in the custom cup game that I lost yesterday. It also shows that fitness that the players lost in that match and indicates that my opponent was using soft tackles ... or maybe normal tackles if I was just lucky to lose less than 2 fitness per player.


The Formulae

The formulae are strewn throughout the spreadsheet as you would expect. The first formula is Section 2 in cell D70 and this calculates the average Q of the squad and is the simple =SUM(D37:D65)/A65. I then have =D70 in cell C70 so that I can see the amount with decimal points.

The next formulae are in Section 4 to calculate the attribute gains. Cell U3 has the formula =D37-D3 which takes the current Q value of my attacker Bae Yoo, 88, and subtracts the season start Q value of Bae Yoo which was Q87, therefore the returning value is 1 which means Bae Yoo has gained +1 Q this season. This subtraction formula runs between columns U and AC in Section 4. It is for this reason that Sections 1 and 2 must not move up or down rows when you are adding or deleting players (ie End of Season must always be on Row 35). Column AD contains the formula =SUM(V3:AC3) (the 3 is for row three so the row below will have 4 instead of 3) which adds the attribute gains together from Keeping to Perception. Column AE has the formula =Y3+Z3+AA3+AC3 for attackers as it adds Sh, He, Sp and Pe together ie the main stats in the red cells. The defenders have the formula =W10+X10+AA10+AB10 as it adds the Tk, Pa, Sp and St of the defenders ie the main stats in the green cells. The next formula in Section 4 is =AE3/AD3*100 which divides the main attributes by the total attributes to show the percentage of gains that are the main attributes. The last formula in Section 4 is at the bottom where it sums the total attribute gains of the squad in each column ie =SUM(U3:U31) which shows that my squad has gained +51 Q this season.

Section five has the two formulae, the first to add up the attribute gains for each player on the right =SUM(V37:AC37) (please note that Q and Fit are not included in this) and then the formula at the bottom of each column to calculate the gains of each stat eg =SUM(U37:U68) will show the total Q gain of the squad since the last copy and paste.

The Macros

There are two macros that operate in this spreadsheet with the second macro being called from the first macro which is started by pressing the "Import New Data" button.



Please continue to the next blog page entitled Setting Up Your Own Excel spreadsheet