Attempting to use Goal Difference as a Tie Breaker (possibly using an IF?)

Hi,

Apologies for the title, but it sums up the problem. Basically it is another sports modelling experiment that I have had (I get the day off work and come up with things like this ), where I just had a curiosity to play out the last games for the top 3 teams in the English Premier League (just to see if I could get some percentages).

The attached, is just the values from one run of my model (I have taken the workings out to reduce the size and for other reasons).

Basically everything to the left of column K, is the model and I am happy with it (and I know goals have to be integers - this is basic);

This plays the games out, and feeds the data into the table at M36 (highlighted yellow), which feeds the data tables at M42 (highlighted green), which are counted to get percentages in the tables (highlighted yellow at M6).

I have put some example output from runs on the second worksheet (so you can see how it works - I like that it suggests Chelsea can win even though it won't be very likely )

Basically you can probably see the problem in cell O22. The percentage chance of finishing first should equal 100% when totalled (e.g. the sum of Liverpool, Chelsea and Man City finishing first, as it has to be one of them should equal 100%). I tracked this back to the fact that I had no way of splitting teams level on points. As a result I added Goal Difference to the model (crudely - e.g. decimal) and here's the question;

How would I go about using Goal Difference to break tie's effectively?

I am thinking I could do some kind of IF function in Q37 (and then have a tie-breaked rank in column R to feed the data tables), on the basis of if anything in N37 to N39 was equal but I am not sure how I would go about applying this.

Any thoughts, advice, nudges in the right direction would be appreciated.

Example Values_EPL.xlsx

Originally Posted by AliGW

I don't follow your logic. O22 does not appear to be incorrect. It is the sum of the top percentages given in that yellow table, isn't it? Why do you think this should add up to 100%?

Sure I probably didn't make this clear - 3 teams, only one can finish first (effectively I'm counting 4th place down as having 0 chance of winning), when you sim out the last games, one of those teams has to win, when you add the percentage that each team finishes first up it should equal 100% (e.g. as one of those teams has to win) - in the way if you add O22, O23 and O24 you get 300%. I hope this makes a bit more sense. I'm figuring with the chances for finishing 2nd or 3rd being a little less than 100%, it is coming down to the fact that I can't split ties.

Originally Posted by AliGW

Surely you need the average of the three first place percentages, don't you?

No. The data table sims it out (effectively I am playing the games on the sheet to the left, 30,000 times, 10,000 times per team as it gives you a fairly stable number at that point - but I linked the Chelsea and the Liverpool game as they aren't independent), and the data tables feed the table at the top. I like the 99.95% for second in O23 - I can live with that as it is probably just down to a slight inconsistency in the modelling (numbers are fairly stable to within 1% at 10,000 runs typically). I was just intending to then match these against the quoted odds to validate the model (so to speak) - I also hold my hand up and am a Chelsea fan (so I was just curious).

Originally Posted by AliGW

OK, José (get it?) - you've lost me. Hopefully someone else will understand what you are doing, but providing a few of your attempted formulae would help us to follow your logic, I think. I hope the season ends well for you!

Thanks for trying.