Sports table/competition formulae

Hi All,

so my first thread (that isn't an introduction). I have a query I am trying to solve but not sure which formulae to use. Best way to sum it up is as follows:

Source:

the central data source(s) are a sports fixture with win/loss progression (i.e. regular rounds then Team 1/2 playoff, winner plays winner of Team 3/4 playoff) until there is a single winner (the length of the regular season is a variable)

The point system is an evenly decrementing scale relative to the total number of teams and allowable points (I.e. 120 points per round over 12 teams for example)


Interpretation:

So first thing I am trying to calculate is the win probability expressed as a percentage (i.e. a win in round 1 = "X%" for the next round with "X" being the relative service factor of points for a win [Win = 12pts = 12/120 = 0.1 = 10%] and this factor accumulates as an average (Win Rd 1 = 12pts + Loss Rd 2 = 6 pts = 18/240 etc).

Next I am trying to calculate the likelihood of winning the season no matter their position on the table, naturally if a team comes dead last in all minus one of the regular season it is statistically impossible if the overarching rules state the top 4 teams are eligible to proceed to the playoff season.

Also, also, also I want to run a scenario builder that would display in a matrix style ('x' & 'y' axis) that shows the current point tally as a sum against probability of winning the overall season.

For reference - I did try to search but without knowing which specific function can do what I require and unable to articulate any technically-correct terminology I was at a loss with results.

Thank you so much for replying, at the moment I've only started writing a non-functioning "script" so the data would be entered later.

It seems it wont let me add any attachments??

can't seem to add images to show what's popping up when i try?? Just a thin white bar, any advice on how to solve?

bump, if anyone can help with this i'd be very grateful.

bump, can anyone help?

Thank you so much, i read the forum rules on my phone and that one didnt come up.

http://gph.is/1ggFnMg

it's on me.

FYI the link is a double-facepalm. But again my sincere thanks for your help.

At the moment the part that I'm getting stuck on, is a formulae to calculate the likelihood of winning, so for example 6 teams with an 8 round regular season; the team at the bottom of the ladder might have a chance at winning the regular season BUT the more rounds played the change reflects to suit.

If my explanation is poor then i apologize but ill segregate as much as i can. Please bear in mind this is a "mapped" model to try and give a clearer picture absent formulae.

Input points
The only area a user would enter data is the rankings for a particular event Sheet! 'Scenario Example'!$A$5:$K$10, and the rest would automatically calculate based on that.

Parameters:

The ranking and points allocated are set as is the number of rounds and games. The teams would be expandable as well.

Objectives:

To set the competition schedule based on available rounds and available teams where each team can meet a set number of times. Track the score and the overall ranking of each team as the season progresses. Finally it would calculate the probability of each team winning the season as the season progresses based on overall score relative to the total number of points available.

@shg - I tried to PM but it didn't seem to even have sent.

Firstly I really hope this finds you well. Secondly, I apologize if my thread was a little off, it was dumped on my lap and I understand what I have to "do" insomuch as how the end result would look but as far as which formulae would best display it and how to use the results I am at a loss.

https://www.excelforum.com/excel-for...ml#post4638392

As I said i understand what I have to "do" as far as the end result however as probability isn't my area of expertise (although I really want to learn more) then trying to define it either verbally or otherwise I suck, so I asked the person who dumped this on my lap to explain what he meant:


"The main thing I need is the final scores for all of the teams based on all of the possible outcomes, aka the permutations of how the tournament could end up.

When I calculate the permutations of 8 teams and 6 games I get 20,160 different possible outcomes. I need all of those outcomes in a dataset. (the final scores of each team). "

So that is simple to read and understand however trying to translate it into excel and which function to use is difficult when I cannot even define the probability function I need to replicate. I did, however, make some headway by using "INDEX" and "CEILING" based formulae (on here I found it on my other machine which I won't be able to get to until tomorrow), and it took the number of different possibilities for VARIABLE 1, VARIABLE 2 and VARIABLE 3 and gave an exhaustive list BUT it gave each variable its own column and it's progress.

From there (I have the table array I believe, just not in a grid matrix) I need to be able to use that in a semi-predictive function that could take generic information (Points/Rounds Played/Wins/Loss) and calculate the percentage of a win.

I did look up and try to implement a POISSON.DIST function but from what I could figure; the data needs to be historical and the data itself would start from nothing (all teams have an equal chance of winning) and will slowly develop itself as more rounds are played. So yet another dilemma is

1) Do I calculate in a table array and base as a LOOKUP reference style formulae to display the results or can it be calculated within the sheet itself and return a figure?

If you have gotten this far, and I don't sound terribly stupid I would appreciate a reply (in case it's not apparent I am at a sincere loss as to how to do this) and I do sincerely wish to learn. I really hope this finds you well, and if I don't speak to you before I hope you have a great day.

Ok I am probably getting way ahead of myself. The analogy is correct and how to calculate the coin flips.

Yes - your assumption is correct, I could ask a simpler question. However would it be relevant to my problem? Up until now I didn't think so.

Except it is:

10 teams
10 competition rounds
50 points maximum for any one team - I need to know how many potential outcomes, but then I need to figure out the likelihood of each outcome.....somehow

This:

https://www.excelforum.com/excel-for...6-numbers.html

You summed it up well with your Cartesian Product Macro.

Given you created it yes it is a safe bet you would.

What it does is it provides a list of all possible outcomes. That is one task

The second is where I will use analogies and examples I am aware of, so bear with me please:

In a game of baseball the game may stop/winner declared before the bottom of the 9th innings because at that point the outcome is already decided (the losing team has no more opportunities at bat thus cannot score any more points) as a probability it would be expressed as 100% chance of winning or statistically it would be expressed as a certainty. To expand on this (and the Analysts at ESPN are probably the guys who do this on a regular basis, or better yet the suppliers of the software that ESPN Analysts use on a regular basis) in a regular season the performance of each team changes based on their games and performance; in an extreme sense after 9 rounds in a 10 round season - a team who has won 9/9 games (expressed as maybe 9/10 games) have a 90% probability of winning the season where a team with 0 wins would have a 0% chance of winning the season. Is that possible to calculate, if so which formulae/function would I use?

@shg - I figured out a solution. Where before i was trying to map out any probability and the likelihood thereof (putting it into ESPN-tier complexity), i instead approached it a little simpler; rather than a predictive process i turned it into a deductive process where adding more data removes possibilities. Instead of asking "what can it be?" i asked "What can it not be?". If that makes sense.