Entire post is OOC.
Hello! Kyle here. I’ve built a spreadsheet-based scorinator for football. I know there are some scorinator tools out there, such as Xkoranate. Why did I feel the need to make one?
First, I wanted to build one that does not require me installing programs. Second, I wanted to be able to customise the formulae and test it myself. And third, I wanted it to be transparent. Why did Person X lose despite having a 3-0 post advantage? Were there other modifiers, or was this purely the result of randomness? I know some of the oldtimers here have had experienced similar issues when the scorination was not particularly transparent. Having a spreadsheet-based scorinator means that everyone can look at the formulae, and see how the result was reached. People can also suggest changes, and then scorinate publicly if they wanted (for instance, they can share screen on Discord while they scorinate.)
And since transparency is a big reason why I did this, this is the link for the scorinator for football (soccer for you Americans ). Feel free to play with it, report a bug, or ask questions to me on Discord. I will explain the formula in the next paragraphs.
Essentially, the scorinating sheet generates two random numbers for (let’s say) Team A and Team B, applies modifiers based on a set criteria, with some randomisation, and then decides the goal difference between the teams. This is because goal difference is a criterion for determining standings both in leagues and groups.
The basic formula is this:
Random Number A – Random Number B + RP Modifier + Skill Modifier = Outcome
Outcome is then used to determine the goal difference depending on the direction (positive or negative) and the percentile it falls on (more on that later).
Important note: If the Outcome is a positive number, it favours Team A; if the Outcome is a negative number, it favours Team B. The RP and Skill Modifiers can be either positive or negative numbers. (more later)
General Settings
Reference cells: “General Settings” in cells A1: B16
There are three general settings:
Randomness Weight, RP Post Weight, and
Skill Weight. You can put number 0-5 in those yellow cells, and this will determine the weight/contribution of the randomness, RP, and skill on the outcome and the percentile cutoffs. If for example, you are RPing a competition between RPers, you might not want to assign skill weights (because it could be controversial) - so you can set it zero. Conversely, if you are simming your domestic leagues, you might want to add a skill weight.
You can play with the weights and set it to your liking. This is a zero-sum game; increasing the number of one component will decrease the contribution of the others. You can see the relative contribution of each component in the
Maximum Theoretical Contribution (cells D1: D4).
RP Post Ceiling is the
maximum number of RP posts that would be credited to the RPer.
The
Maximum Theoretical Outcome is simply the largest possible total outcome of the entire process. This is used to compute for the percentile cutoffs for each goal difference possibilities (more on this later).
“Score Now!” cells
(A15:B16) is simply a tool to reveal the scores when you are ready to scorinate. The drawback with spreadsheet-based scorinators is that they rely on random number generators; and random number generators recalculate and give a different output everything you change something in a spreadsheet. So to make it less confusing, the outcome is only shown if you put YES in cell B15. So in order to scorinate, fill up the yellow cells first, and then type YES in Cell B15.
Since the sheet recalculates everytime you change something, if you want a permanent record, this is what you will do:
- Make a new sheet and label it accordingly.
- Go back to the scorinating sheet.
- Fill out the yellow boxes. For RP Posts and Skill Points, only integers 0-5 are accepted. Once happy with the data inputted, type YES in cell B15. You will now get the result output.
- Select the rows that you want a permanent record (there is a guide line).
- Right click the selection and COPY (if using Windows, you can use Ctrl+C)
- Go to the new sheet.
- On the Menu Bar, select Edit > Paste Special > Paste values only.
- This will give you a permanent record of the scorinator. And if you go back to the scorinating sheet, the values there will now be different than the one you just pasted. This is not a bug; like I said, the random number generator recalculates everytime you changed something in the workbook.
How does the scorinator come up with a result?
Random Number
Reference cells: “Random Components” in cells L20:S21
Let’s take Team A first. The
BaseScoreA is a random number for team A. This is determined by three random numbers,
RandomA1,
Random A2, and
RandProportionA. RandomA1 and RandomA2 are anywhere from 0 to the
UpperRandomLimit, which is based on the
Randomness Weight. The larger the
Randomness Weight, the larger the
UpperRandomLimit. For example, a Randomness Weight of 3 will have an UpperRandomLimit of 600, which means RandomA1 and Random A2 will be from 0 to 600.
RandProportionA is a number between 25 and 75 that used to combine the two random numbers randomly. This is to minimise BaseScoreA being just based on the vagaries of a single unlucky draw. What
RandProportionA does is that it determines how much of
RandomA1 and how much of
RandomA2 will be used to determine the
BaseScoreA. This is the formula:
(RandomA1 * RandProportionA / 100) + [RandomA2 * (100 – RandProportionA) / 100] = BaseScoreA
The same process, but with different random number generators, are used for Team B’s random number
BaseScoreB.
RP Post Modifier
Reference cells: “RP and Skill Bonuses” in cells L22:R23.
The
RPEffect is dependent on three numbers:
- PostDifference. This is the difference in the number of posts between the two players.
- RPWtProportion. This is another random number that is generated between RPWtLower and RPWtUpper. These two numbers are generated based on the number of posts the less active player made. If the less active player made zero posts, RPWtProportion will be a random number between 50 and 100. If the less active player made one post, RPWtProportion will be 43 and 83. For example, this means that a 3-2 post difference is likely to generate a smaller RPEffect than a 1-0 post difference, even though in both cases the more active player has one post more. This ensures that some credit will be given to the less active player who posted, by making the RP Effect lower.
- RPMultiplier. This is the product of RPWtProportion and RP Post Weight.
This is the formula for the
RPEffect:
RPWtProportion * RP Post Weight * PostDifference = RP Effect
Based on how
PostDifference is structured, if A has more posts than B, the
RPEffect will be a positive number; conversely, if B has more posts than A, the
RPEffect will be a negative number. If
PostDifference is zero, then
RPEffect is also zero.
Skill Modifier
Reference cells: “RP and Skill Bonuses” in cells S22:U23.
The
SkillEffect is dependent on three numbers:
- SkillDifference. This is the difference in the number of skill points between the two teams.
- SkillWtProportion. This is a random number between 50 to 100.
- SkillMultiplier. This is the product of SkillWtProportion and Skill Post Weight.
This is the formula for the
SkillEffect:
SkillWtProportion * Skill Weight * SkillDifference = SkillEffect
Based on how
SkillDifference is structured, if A has more posts than B, the
SkillEffect will be a positive number; conversely, if B has more posts than A, the
SkillEffect will be a negative number. If
SkillDifference is zero, then
SkillEffect is also zero.
If you set
Skill Weight is set to zero, then
SkillEffect is also zero.
Outcome and Percentiles
Reference cells: “Outcome” in cells E20:J22; for percentile cutoffs, the green block from E1:Q14.
The formula for the
Outcome is given earlier. This
Outcome is then compared to what percentile it falls on based on the
MaximumTheoreticalOutcome. If the
Outcome falls between the 8th to 23rd percentile, the goal difference is 1; if the
Outcome falls between the 24th to 38th percentile, the goal difference is 2; and so on. Notice that the intervals decrease as you go higher; this is because high goal differences are less likely and this models for that. (For instance, for a goal difference of 10, the
Outcome needs to fall in the 99th percentile and up.) This goes for either direction.
Score
Reference cells: “Score Creator” in cells L24:X25.
The game’s score will now be created based on the goal difference. First, a number
LoserGoals will be generated for the number of goals for the losing team. This is not based on a single number generator; if we would base it on that, we can get ridiculous scores like 9-8 for a goal difference of 1.
LoserGoals is generated by using 5 random number generators (
Seed10) that will give out a number between -10 and +10. Then the values are added and the absolute value is taken. This would produce some sort of a gaussian distribution in which the most probable value for
LoserGoals would be 0, followed by 1, etc. This would be consistent with the number of losing team goals seen in professional games.
Then to get the number of goals of the winner, the goal difference will be added to
LoserGoals. The
WinnerGoals and
LoserGoals are then assigned to the respective teams.
The sheet also calculates halftime scores. This is merely a random number generated between zero and the number of goals of the team.
But in football, sometimes there are no winners or losers. There are ties. How does the scorinator simulate that?
Ties
Now, there will be a tie (goal difference of zero) if
Outcome falls between 0th to 8th percentile in both directions (giving an interval of 17, as opposed to GD of 1 which has an interval of 15). How will ties be resolved?
Some games (ex. group games in world cups, domestic leagues which are non-knockout tournaments) do not require a tie to be broken. In others, such as knockout or playoff games, a tied game is required to be broken. This condition needs to be answered in
“Need to force win?” (cells A28:B28). If any resulting ties needs to be broken (ie. A knockout game or playoff), answer B28 with YES; otherwise, the default answer is NO.
AET
In some games, they extend the time of the game. (AET = after extended time) This is simulated if the score falls between the 8th and 10th percentiles, in which the game with a goal difference of 1 is marked as won after extended time if the tournament has that rule; otherwise you can ignore it and just treat it as a normal win.
Penalty Shootouts
Reference cells: “Penalty Shoutout Randomiser” in cells L26:U28.
If
“Need to force win?” is YES and there is a tie, a result would appear in the
Penalty Shootout area
(cell A27:C27). This is calculated in the
“Penalty Shoutout Randomiser” area. Basically it simulates the penalty shootout of five people in each team trying to score a goal. Thus, for each team, there are five random number generators that gives either a value of 1 (a goal) or 0 (a miss). The total is then taken, which will be the Penalty Shootout Score.
If there is still a tie, the scorinator will automatically break the tie based on the
Outcome score.
Last Notes
So I hope this long post explains how the scorinator works. If you have any questions/suggestions, feel free to message me on Discord. I am not scorinating this World Cup, somebody else will do it.
Also, if you want to use the scorinator for your own simulations, feel free to do so! Just credit me for it
) or poorly-worldbuilt entries. The rest of the blanks shall be filled up with host country’s companies at the end if nobody took the slot.
