Abstract

The Talent-Finder spreadsheet is composed of the Wage Calc and the Training Calc, although both are focussed on finding out who is a potential talented player, and how you can go about improving that talent.
This is actually a significant re-write of the spreadsheets previously known by many as “Scuffer and Tuhob BT Wages and Training Calc”, and for the most part, the front end hasn’t changed that much. The back-end has though, and includes a brand new wage formula that is much more accurate than the previous version. The other changes can be seen below.
Why the new name then, if it’s just a re-hash of an existing tool? Adding on all the developers names was making the filename very long!
The spreadsheets are a collaboration over time between many users, myself included. The Wage Calc was first created by Scuffer, based on work for the formula by gravy118. I helped to fix one or two things at the back, and helped designed the front-end that now exists. This version sees Zinc providing and integrating the new formula, with me fixing up a couple of things along the way.
The Training Calc was created by Tuhob (front-end re-designed by me), with one or two bug fixes and extensions in this version by Zinc and myself.

Download

There are two versions of the Talent-Finder, due to Excel’s macro code and OpenOffice’s macro code being different.

As the spreadsheet relies on macros, you must Enable Macros when you open the file for it to all work properly.

For the security conscious, the macros provides the copying ability for the Import, Export and Copy Current To Desired buttons, and the goal seek functions for the Estimate buttons. There are also two functions, these being the actual primary and secondary wage formulae themselves. If you are still unsure, then open the file, but choose Disable Macros, then look at the macro code to be confident there’s nothing malicious going on.

Option 1 — Microsoft Excel and NeoOffice

Talent-Finder.xls (155KB). Downloaded times since .

Option 2 — OpenOffice Calc

Talent-Finder.ods (53KB). Downloaded times since .

Option 3 — Microsoft Excel for the Mac

Talent-Finder-Mac.xls (135KB). Downloaded times since .

Changelog

Wage Calc

Version 5.2 (2008-03-09)

• Specific version for the Mac, with the buttons now working. (zinc)
• Clearer instructions, especially for first time users. (zinc, Paysie, Jargon)

Version 5.1 (2008-02-01)

• Removed one line of VB code to ensure compatibility with NeoOffice. Also stops the number flickering effect. (zinc)
• Fixed Export/Estimate buttons broken by zinc (Jargon)
• Changed the Form Control Buttons to ControlBox buttons, and added images. (Jargon)
• Substantial tidying/simplification of VB code.
• Secondary Fraction changed to Secondary Percentage in calc and Instructions (People are less scared with % than fractions, and % are easier to style than fractions too). (Jargon)
• Renamed title for Wage page. (Jargon)
• OpenOffice Calc version created. (Jargon)

Version 5.0 (2008-01-26)

• Changed Wage formula to the most accurate version yet. (zinc)
• Re-factored implementation of formula, to make future changes far easier. (zinc)

Version 4

• Unofficial release with new versions of wage formula for secondary skills, to explain deviations from the old ‘secondary=20% of primary’ rule in players with high secondaries. (zinc)

Version 3

• Unofficial release with new versions of wage formula for primary skills. (zinc)

Version 2.1 (2007-10-04)

• Stamina bug, spotted by Fishy. (Scuffer)

Version 2.0

• Front-end re-designed to improve UI. (Jargon)
• Some refactoring of VB code. (Jargon)

Version 1.0

• Initial release. (Scuffer)

Training Calc

Version 2.2 (2008-02-05)

• Fixed bug that showed different results with 0 or an empty cell in the training nets. (zinc)

Version 2.1 (2008-02-01)

• Fixed bug with Estimated Levels at end of Season/next Season/Season After (incorrect lookup range). (Jargon)
• Fixed bug to do with Wicket Keeping and consistency. (zinc)
• Fixed Import/CopytoCurrent buttons broken by zinc (Jargon)
• Changed the Form Control Buttons to Control Box buttons, and added images. (Jargon)
• Substantial tidying/simplification of VB code. (Jargon)
• Training data is further extrapolated to age 35. (Jargon)
• All formulae that refer up to training data to age 30, now refer to age 35. (Jargon)
• Current week on Training Calc is now calculated automatically. (Jargon)
• Calculated Training levels now show to one decimal place. (Jargon)
• Training levels input by user now show to two decimal places. (Jargon)
• Validation added to Training Weeks box. (Jargon)
• Validation changed to Nets boxes. (Jargon)
• Skills levels extended to Elite++++++++++, but hidden beyond Elite. (Jargon)
• OpenOffice Calc version created. (Jargon)

Version 2.00 (2007-10-04)

• Front-end re-designed to improve UI. (Jargon)
• Added ability to work out skills at end of season and following seasons. (Tuhob)
• Added ability to use sublevels of the skill levels. (Tuhob)

Version 1.35

• Added Export function to Wages calc. (Tuhob / Scuffer)

Version 1.3

• Combined wages and training calculators. Added import and copy functions. (Tuhob / Scuffer)

Version 1.25

• Updated training times for 3 x batting/bowling nets (Ages 17 -23). (Tuhob)

Version 1.2

• Added some rough training times for 3x batting/bowling nets (Ages 17 – 19). (Tuhob)

Zinc and Scuffer’s Wage Calculator

(Click image for bigger screenshot)

Instructions for estimation

• Enter your best estimate into each of the “estimated level” boxes, eg. if you think a player is mid-proficient enter 8.5
• Make sure you include a reasonable estimate (within a couple of levels) of the skill you want to know otherwise it may be too hard for excel to calculate (a minimisation routine is used rather than an analytic solution).
• Enter the player’s wage into the Target Wage box.
• Click the relevant Estimate button.
• The approximate level is displayed in the relevant box.
• You can export the skills you have entered and copy them to the Training calc by pressing the “Export to Training calc” button.

Min/Max info

• If you don’t know sublevels, the best you can hope for is a wage range.
• If you don’t know the sublevel of a skill, leave the box ticked. If you do know, untick it.
• The min and max wages boxes will give you an idea of whether actual wage is nearer minimum or maximum.
• Try varying the sublevels of the largest contributor(s) to the player’s wage to get a loose estimate.
• In this way, it can help determine whether a player is near a pop.

Problems

• “Clicking the estimate button gives a tiny number” — The contributions of other factors exceed the target wage. Correct either of these, or possibly alter the secondary contribution.
• “It gives the wrong wage” —Remember that a players wage is only accurate either at the wage update or if they have not received any training since the wage update.
• If you are sure the wage shown is wrong please contact zinc with the player details in game.

Scuffer, manager of Scuffershire CCC (3489)

Notes from zinc

The secondary contribution is related to the primary contribution of the same level by the following:

secondary contribution = primary contribution (secondary percentage * level + secondary constant)

It seems likely that both the secondary percentage and the secondary constant are the same number, however at the moment I don’t have sufficient data to show that conclusively so you are free to vary them both independently, eg. percentage = 70% and constant = 0.45 gives a fit that may work. You can contact zinc for alternative values. The fit is very sensitive to the secondary percentage so don’t change this by more than 3%. Also 67% fits better than exactly 2/3.
We haven’t locked any of this down, so you are absolutely free to adapt/improve any aspect you like. We would be grateful of a (small) acknowledgement if you distribute any improved version.

This spreadsheet is a very compressed version of the data I’ve collected. In it the secondary data may be slightly less reliable than the primary data and is being re-evaluated. Errors on both primary and secondary data above proficient increase with level since working out a higher level minimum wage requires subtraction of lower level skills. Data is accumulated from confirmed untrained players on the transfer market.

Testing: Collectively around 500 players with a single stat above abysmal and a further 500 untrained players with mixed stats on the transfer market – all have wage within predicted range
Individual Players – around 20 players with very well known sublevels, all bar 1 wage prediction accurate to within 1-2% (further testing is welcome!)

I’d like to thank the members of the CM Old Boys and Jargon’s Battrick Tools Alliances for testing of the formulae. Also all the managers who replied to my emails about player stats on the market.

Zinc, manager of Little Wooping CC (4692)

Tuhob’s Training Calc

(Click image for bigger screenshot)

Functions

There are two functions to this calculator:

1. Determine how long it would take to train a player to a certain skill level.
2. Determine what a player’s estimated skill levels would be after training for a set number of weeks.

How to Use

1. First put in the age of the player in the “Age” box.
2. Then put in the current skills of the player in the appropriate “Current level” boxes. Note, you need to put in the number corresponding to that skill level. Refer to the skills table. Alternatively, if you have filled in the skills in the Wages Calculator you can import them into the Current skills section by pressing the Import button or by pressing the Export button on the Wages Calc sheet.
3. Now put in the training nets that the player will be receiving. Eg Put a 1 in the “Bat” net box if the player will be on 1 batting net, 2 if they will have 2 batting nets. Note, you can put in 3x batting/bowling nets but the data is only available for 17 – 23 year old players.
4. Now if you would like to determine how long it would take to train a player to a certain skill level, fill in the “desired level” skill boxes and you should then see a corresponding answer in the “No. of weeks to reach” box. Note, since a players age affects training times, the results will not be accurate if the number of weeks to reach will run over into a new season.
5. For an estimate of what a player’s skill levels will be after being trained for a set number of weeks, fill in the number of weeks in the “No. of Training wks” box and you will see the estimated levels in the table below.
6. For an estimate of what a player’s skills will be at the end of the current season or at the end of the next or following season, you can refer to the bottom table. It takes into account aging and the resultant different training times. Although the numerical figure would be correct, the descriptor only goes up to Elite++++++++++.

Limitations

• Some Training data is extrapolated eg (age 24 and above, effect of fielding on concentration/consistency, 3 bat/bowl nets for 17 -19) or missing (times for 3 bat/bowl nets for 24+). So the results are, at best, an estimate.
• Estimating how long a player will take to reach a skill level does not take into account overlapping into the next season so it is slightly inaccurate. However you can get a rough guide by looking at the estimated skill levels in the end of ‘x’ season table.

Tuhob, manager of the Tassie Titans (6281)