New Sim (v2)

Kicked Penalties. Both kick at goal and kick for touch both now working.

Just had a move with Scrum ---> Penalty ---> Kick for Touch ---> Line Out ---> Off Side ---> Kick at Goal ---> Three Points!

 

Yup.

 

Well, mostly using RAND() and a 'TRUE' VLOOKUP to simulate the roll of a dice based on fractions and then having weighted outcomes based on the skill sets of individuals or groups of players depending on what's required. Then masses of stacked IF statements to determine the outcome based on a set number of variables.

 

A sample of eggs:

=IF(B34="","",IF(B34="Kick at Goal",(RAND()*(1/(51-ABS(B22))*200)*(VLOOKUP(B26,'Team Stats'!$A$2:$AH$5,19,FALSE))),IF(B34="Kick for Touch",(VLOOKUP(B26,'Team Stats'!$A$1:$AI$5,35,FALSE)*RAND()),"Error")))

This formula is evaluating what your stat would be for taking a penalty kick. It is taking the result of the last play (in cell B34) and saying if it's a kick at goal (assessed based on distance from the posts) then calculate a difficulty factor based on distance from the posts (1/51-ABS(Distance from Posts)*200) and multiply by skill level (the vlookup). The 2nd If statement is what will calculate if cell B34 has resulted in a "Kick for Touch". This time it just takes the team's stat for tactical place kicking (I'm not telling how I worked this out) and then a random factor and calculates your kicking chance.

In the next cell, the result of this formula is then assessed to determine whether the kick succeeded or failed and thus whether a penalty has been scored or whether touch was successfully found with a tactical kick.

 

Only problem will be that it won't write commentary for you. Anyway, here's a quick sim that I'll do for you (without a kick off)

The ball's gone into touch off a Hurricanes player. Poor handling there.
Eddie Morris is set up to throw at the line out.
Straight to Simon Laing.
The whistle's gone. That's been called not straight. And fair enough too. Poor throw from Morris.

Scrum down. Hurricanes to feed.
A decent contact for the Hurricanes, but they're gaining no traction against a concerted effort from the Sharks front row.
The ball comes clear slowly, and the Sharks backs come in quickly and a ruck is formed. The Hurricanes gained only a paltrey metre from that effort

[end]

Obviously expanding on the numbers the sim comes out with as it only tells me (1) The ball went into touch (2) Sharks Throw (3) Not Straight Line Out (4) Hurricanes Scrum - gains one metre (5) Open play - ruck is formed immediately

And as I haven't programmed a ruck yet, that's where it ends.

 

And I'll probably work on rucks next as they're the most common non-open plays.

Which will be interesting as I've got to decide how to work out which individuals come to the Ruck first.

So, I need to calculate a weighting of likelihood on a player entering the ruck based on (1) Speed (2) Strength (3) Tight Defense (4) The desire to have a certain player at the Ruck.

So, out of all XV players... Who would you most like to arrive at the ruck first? If someone could list the players in order, that'd be cool.

 

Flankers & #8 not ahead of the Locks?

 

I've come up with these percentages. Overall, it means (ignoring the stats of individual players) that you'll get a Forward to the Ruck 91% of the time and a back only 9% of the time.

Loosehead Prop 15%
Hooker 14%
Tighthead Prop 15%
Second Row 12%
Second Row 12%
Blindside Flanker 8%
Openside Flanker 8%
Number 8 8%
Scrum Half 1%
Fly Half 1%
Wing 1%
Inside Centre 2%
Outside Centre 2%
Wing 1%
Full Back 1%

 

Once you've factored in speed (which is much likely to be higher for backs than forwards), I hope the split comes to about 80/20 (obviously depending on the relative quality of your backs vs. forwards)

 

No, more akin to if you've got slow, rubbishy lumbering forwards, then they will take longer to get to the ruck and thus the back will be forced to take their place. In which case, it's more likely that your best back will be forced to fill in.

I'll look at the individual player's speed as a factor of the whole team's speed

 

Hit some trouble. Yes, I can select a number of players from the list of players on a team based on these weighted factors... However, when I select the second player, I can't exclude the one I've already selected. Hmmm... Must be a way of doing mutually exclusive probabilities in Excel... A question for another day, methinks.