How to create your own spare ratings spreadsheet

[valkorum]

I am gonna try and write something so people can create their own spreadsheet (excel only, not open office) to work out spare ratings.  Row 1 will be used for titles so my descriptions will look like so:


Column A - What column to use
Player - Name of title for that colum
{Details} - what to type in that column (dont type the squiggle brackets)




Column A
Player
{Players name}

Column B
Endurance
{Skill Level (ie. Expert)}

Column C
Endurance Hide
{=VLOOKUP(B2,V30:W45,2,FALSE)}

Column D
Batting
{Skill Level (ie. Poor)}

Column E
Batting Hide
{=VLOOKUP(D2,V30:W45,2,FALSE)}

Column F
Bowling
{Skill Level (ie. Accomplished)}

Column G
Bowling Hide
{=VLOOKUP(F2,V30:W45,2,FALSE)}

Column H
Tech
{Skill Level (ie. Dreadful)}

Column I
Tech Hide
{=VLOOKUP(H2,V30:W45,2,FALSE)}

Column J
Power
{Skill Level (ie. Reliable)}

Column K
Power Hide
{=VLOOKUP(J2,V30:W45,2,FALSE)}

Column L
Keeping
{Skill Level (ie. Capable)}

Column M
Keeping Hide
{=VLOOKUP(L2,V30:W45,2,FALSE)}

Column N
Fielding
{Skill Level (ie. Reasonable)}

Column O
Fiedling Hide
{=VLOOKUP(N2,V30:W45,2,FALSE)}


(For column C, E, G, I, K, M, O.... The value of B2, D2, F2 etc - you will need to change the number 2 to be the row that you are on.  The formula for these columns wont work until you have completed columns B, D, F, H, J, L, N, V, W.)


Column P
Total
{=C2+E2+G2+I2+K2+M2+O2}
(you will need to change the number 2 to be the row that you are on)

Column Q
Rating
{Put the players current rating here}

Column R
Spare
{=Q2-P2}
(You will need to change the number 2 to be the row that you are on)


Column V (starting at row 30)
Atrocious
Dreadful
Poor
Ordinary
Reasonable
Average
Capable
Reliable
Accomplished
Expert
Outstanding
Spectacular
Exceptional
World Class
Elite
Legendary


Column W (starting at row 30)
0
1,000
2,000
3,000
5,000
4,000
6,000
7,000
8,000
9,000
10,000
11,000
12,000
13,000
14,000
15,000



When typing in any skill level you must use a capital for the first letter.


To make it easier to read - hide columns, C, E, G, I, K, M, O, P, V, W

At each training update you will need to add in any pops to skills (ie Reliable to Accomplished) and the players new rating.  Your new spare rating will be auto-calculated.

I suggest using 2 workbooks (1 for senior and 1 for youth).  Otherwise you may run in to problems if you have a total squad larger than 30 players.


HOPE THIS HELPS :)

[8-6 Suited]

Nope, doesn't help at all. I can't stand excel so this all went over my head. :P

[valkorum]

Nope, doesn't help at all. I can't stand excel so this all went over my head. :P

Pfft.  I tell you exactly what to type - you dont have to think :P

[Fletch74]

I just download it from the Stats section as a numbered format, then add a column after fielding and get the rating total less each skill section.

The download in numbered format shows the skill level as 1 for dreadful, etc. I use the formula

=G2-(SUM(H2:N2)*1000) - This multiplie the skill by 1,000, then deducts it from the overall rating, giving me my spare rating.

[valkorum]

I wasnt aware that you could download the details :(

Your way works too

[8-6 Suited]

That doesn't help me either. I know you told me what to type but there is a reason I am thick when it comes to excel... Its shower and I don't understand anything to do with it. Never have, never will.

[Justin Bieber]

That seems a little too complicated for me :(.

I just put the name, rating, used ratings and ends with spare ratings :).

[Boomz]

I hate excel but good explanation...

[8-6 Suited]

There is an easier way to do it...



DAAAAAAAAAAZZZZZZZZZZZZZZZ Can you do mine pleaaaaasseeeeeee!~!! works too. :p

[bomberboy0618]

I just download it from the Stats section as a numbered format, then add a column after fielding and get the rating total less each skill section.

The download in numbered format shows the skill level as 1 for dreadful, etc. I use the formula
=G2-(SUM(H2:N2)*1000) - This multiplie the skill by 1,000, then deducts it from the overall rating, giving me my spare rating.

So that calculates the spare ratings for each skill?

[valkorum]

That doesn't help me either. I know you told me what to type but there is a reason I am thick when it comes to excel... Its shower and I don't understand anything to do with it. Never have, never will.

Excel is quite powerful - you just gotta know how to use it ;)

I wish Fletch had of told me that you can download the stats before I spent the time to write it out :P

[valkorum]

Mine still looks nicer :P

[bomberboy0618]

i can use it :)

[cookie311993]

cheers for that val. just tested it to see if i could work it and i can. good job

[MajorLazer]

How do you download the stats?