Tricky formula to calculate luck - with Sample attached

[olimajor123]

Hi there,

I am running a fantasy football league in which the factor of how many goals you concede each week is uncontrollable and thus is down to luck. I want to create a forumla which calculates the luck element in order to then rank the luckiest teams.

See the attached sample:

The main bulk is the scores between the teams (A - J), these show the scores each gameweek (currently 11 have passed, there will be 38 in total). If you scroll down to cell J64 there is a table that will be linked to another sheet on the actual version which shows how many goals each side has scored in the gameweeks.

What I want to do is calulate the average points a team would get scoring a certain amount of goals against a certain side. For example, take cell F29 as a good example. That week, team E scored 2 goals against Team B, but lost 4-2. I want to calculate (in cell H29) the average number of points Team E would have received if they had scored 2 goals against Team B in the 11 gameweeks so far (using 3pts for a win, 1 for a draw, and 0 for a loss), eg the result may be a number like 1.32

Where this becomes tricky is that I want the forumla to adapt when I add the gameweek 12 results next week and the gameweek 12 figures update in the J64 table.

The ultimate goal in this is to then see the average number of points teams receive, against their actual total to calculate their luck rank.

I hope this makes sense. The sample attached should help provide clarity. Any questions please ask.

Sample - https://www.dropbox.com/s/06tsgzh0mcbadd1/Sample.xlsx?dl=0

 

[synergy16]

sounds like a fun project. ill take a crack at it. might (and by might, I mean most likely will) have questions for you

 

[synergy16]

its funny, cause when I first read this I thought you meant fantasy American football. and I was wondering what you were talking about with goals.

 

[synergy16]

"What I want to do is calulate the average points a team would get scoring a certain amount of goals against a certain side"

how am I to know what week's score for a particular team is going to be used as their "base number"

 

[olimajor123]

"What I want to do is calulate the average points a team would get scoring a certain amount of goals against a certain side"

how am I to know what week's score for a particular team is going to be used as their "base number"

Hey, thanks for responding. Yeah I mean soccer, forgot that's how it's know in the US.

So take cell F29 as an example. That week team E scored 2 goals, but lost to Team B 4-2. I want to calculate using the 2 goals Team B scored, how many times the 2 goals would have been enough for a win or a tie against team B based on how many goals Team B has scored in each of the previous weeks (shown in the J64 table). Ie, in the first week it may have been good enough for a win if team B only scored 1 or 0 that week, so 3 points, but in week 2 it may have got him a tie if Team B scored 2 that week, so 1 point, etc. The result will be a figure between 0 and 3, as it will be the average points across the weeks played so far.

Hope that helps. It's one of those things that seems easy to explain when you know what you're trying to do but in reality probably isnt!

 

[synergy16]

yes I understand what you are trying to get at, but my point was, you used F29 as an example...when they scored 2 goals. that's 1 team, 1 week. you used that score as a base for all your calculations but what I'm asking is what do I use for a base for all other teams? lets say for example. team D...what weeks score do I use....? what about team B? will that change? do you know what I'm saying?

 

[olimajor123]

yes I understand what you are trying to get at, but my point was, you used F29 as an example...when they scored 2 goals. that's 1 team, 1 week. you used that score as a base for all your calculations but what I'm asking is what do I use for a base for all other teams? lets say for example. team D...what weeks score do I use....? what about team B? will that change? do you know what I'm saying?

I want to do it for each matchup. So with F29, if that happened in Gameweek 6 for example, I want to find out based on the previous 5 gameweeks how many times the 2 goals Team E scored would have been good enough for a win against Team B. So for example, in the preceeding 5 gameweeks, Team B scored 1,2,2,2,3 goals, so Team E's two goals would have got them win,tie,tie,tie,loss with those two goals, so (3pts,1pt,1pt,1pt,0pt)/5 = 1.2.

1.2 would be the value I want to return as it is the average points the two goals Team E scored against Team B that would week have got them based on Team B's performance in the preceeding gameweeks. But they got 0 points.

In gameweek 7 you would look up the goals scored by the teams for the previous 6 gameweeks etc.

The table at J64 is an autoupdate tally of the goals each team scores each week so can be used in the forumla to look up the goals for the previous weeks.


I don't know if there is definitely a formula to do this. My excel experience usually tells me there is, but I just don't know it.