Help needed with Excel.

[Interesting Ian]

Sure, the Poisson Theorem is a way to estimate the probability of an event happening k times within n tries. It is written as:

[qimg]http://mathworld.wolfram.com/images/equations/PoissonTheorem/equation1.gif[/qimg]
Eric W. Weisstein. "Poisson Theorem." From MathWorld--A Wolfram Web Resource. http://mathworld.wolfram.com/PoissonTheorem.html

Damn it! I'm drunk at the moment and I have no idea what it could possibly mean to talk about n tries in the context of a football match!

The first equation you may recognize as the equation for finding the probability of an event happening k times within a binomial distribution.

No, not at all. I have no idea what a "binomial distribution" is.

This bloody e keeps coming up though! Why don't we use base 10? I guess becasue this e has a certain specific relationship to pi . . .hmmmm.

I'll think about this tomorrow when I'm sober.

I have a feeling I'll now be able to solve this half-time/full-time business, but I need to be clear headed.

 

[hodgy]

Which computers forum? Yes I could ask somewhere and hopefully someone will do it for me.

The one on JREF!

Also look here: http://www.cpearson.com/excel/ontime.htm

 

[Robin]

Hi, does anyone know what this poisson equation is?

Let's suppose x represents the number of goals by a team and y is the pre-match expectation of how many goals that this team will score. What would be the equation for the probabity of x just for this one team?

Doing it is easy - understanding what it means is the tricky part and I would not want to lead you astray in this respect as it is not a measure I am overly familiar with.

But suppose you have the pre-match expectation in B2 and the number of goals in B3 then all you need to do is

=poisson(b3,b2,false)

Whether this is meaningful in this context I couldn't tell you.

When you look up a function on the Excel toolbar then click "Help on this function" it will tell you the actual formula.

 

[Interesting Ian]

Doing it is easy - understanding what it means is the tricky part and I would not want to lead you astray in this respect as it is not a measure I am overly familiar with.

But suppose you have the pre-match expectation in B2 and the number of goals in B3 then all you need to do is

=poisson(b3,b2,false)

Whether this is meaningful in this context I couldn't tell you.

When you look up a function on the Excel toolbar then click "Help on this function" it will tell you the actual formula.

Yes I know that the poisson function is built in. But I wanted to see what it is to try and clarify what I'm actually doing. It does indeed list it in the help, so that's great.

 

[Interesting Ian]

I have a feeling I'll now be able to solve this half-time/full-time business, but I need to be clear headed.

Well now I'm sober I realise that I can't!

Unless someone can help me I'm going to have to give up on this.

I'll just spell out what I was thinking yesterday.

I'm trying to work out the probability that team A will be leading both at half time and full time.

Let a1 and a2 be Team A's score at half time and full time respectively.

Let b1 and b2 be Team B's score at half time and full time respectively.

Now on average 44% of all goals are scored in the 1st half and 56% in the second half.

Let x be the prematch expectation of goals for Team A at half time.
Let y be the prematch expectation of goals for Team B at half time.

But this also means that the prematch expectation of goals at full-time will be 100x/44 for Team A, and 100y/44 for team B.

What I need to do is find the probability where:

a1 > b1

AND

a2 > b2



P(a1) = poisson(a1,x,false)

P(b1) = poisson(b1,y,false)

P(a2) = poisson(a2,100x/44,false)

P(b2) = poisson(b2,100y/44,false)

Now last night I was foolishly thinking (but I was drunk!) that I simply needed to find out the probability that

P(a1) > P(b1) AND P(a2) > P(b2)

But that's wrong because that effectively treating the match up to the end of the first half, and up to the end of full time as two separate matches.

But they are not independent events because if Team A is leading at half time then that substantially increases the chance of them winning at full-time.

At this point in time I'm just feeling confused. I'm on the verge of giving it up. I'll just have to leave the half-time/full-time market alone.

Unless I email Betfair, maybe they'll know how to calculate it . . .hmmm

 

[joe87]

Actually, wouldn't a drop of 23% be 26 standard deviations?
.99^26=77%

Yes it would, but I was being a little cavalier with the standard deviation value. If "on the order of 1%" is actually 1.13%, then the numbers work out to 23 standard deviations. Either way, the implied odds are beyond astronomical.

 

[Interesting Ian]

Well now I'm sober I realise that I can't!


Let a1 and a2 be Team A's score at half time and full time respectively.

Let b1 and b2 be Team B's score at half time and full time respectively.

Hang on a sec, I've just had a thought.

Let a3 be Team A's score for the 2nd half and b3 be Team B's score in the 2nd half (the 2nd half considered as a separate match so to speak)

Now I said before that the prob that Team A will be in the lead both at half time and full time is:

The prob that Team A wins 1st half * the prob that Team A wins 2nd half
+
The prob that Team A wins 1st half * the Prob the 2nd half is drawed
+
The prob that Team A wins 1st half * the Prob Team B wins second half by less than Team A's first half lead.


Team A's first half lead will be (a1 - b1)

Team B's second half lead will be (b3 - a3)

So the prob will equal:

The prob that Team A wins 1st half * the prob that Team A wins 2nd half
+
The prob that Team A wins 1st half * the Prob the 2nd half is drawed

and if a1 - b1 > b3 - a3

then add

The prob that Team A wins 1st half * the Prob Team B wins second half.

Hmmm . . .am I getting close here?

 

[Interesting Ian]

Who would have thought one needs to be a mathematical genius just to gamble!

 

[Interesting Ian]

Hang on a sec, I've just had a thought.

Let a3 be Team A's score for the 2nd half and b3 be Team B's score in the 2nd half (the 2nd half considered as a separate match so to speak)

Now I said before that the prob that Team A will be in the lead both at half time and full time is:

The prob that Team A wins 1st half * the prob that Team A wins 2nd half
+
The prob that Team A wins 1st half * the Prob the 2nd half is drawed
+
The prob that Team A wins 1st half * the Prob Team B wins second half by less than Team A's first half lead.


Team A's first half lead will be (a1 - b1)

Team B's second half lead will be (b3 - a3)

So the prob will equal:

The prob that Team A wins 1st half * the prob that Team A wins 2nd half
+
The prob that Team A wins 1st half * the Prob the 2nd half is drawed

and if a1 - b1 > b3 - a3

then add

The prob that Team A wins 1st half * the Prob Team B wins second half.

Hmmm . . .am I getting close here?

I've just thought. I'll also need to specify that a1 - b1 > 0 AND b3 - a3 > 0

Hmmmph . .even if I've got all this right I'll never be able to program Excel to calculate all this

 

[T'ai Chi]

Who would have thought one needs to be a mathematical genius just to gamble!

I'd say you need to know math to not gamble.

 

[joe87]

Who would have thought one needs to be a mathematical genius just to gamble!

Any idiot can gamble. To have an edge, it helps to be able to analyze the game and the odds. To do this, you need some background in math and statistics.

Ian, like it or not, you would benefit from learning some basic math and statistics terminology and concepts. Otherwise, when you try to discuss these concepts with people who are knowledgeable about such things, you won't be able to understand them. Asking them to dumb down what they are saying so you can grasp it is unrealistic - they won't bother.

Innumeracy can be as big a disadvantage as illiteracy.

 

[69dodge]

Taking the average odds that bookies quote will be fairly accurate for the match outcome I would imagine. But not say for something like correct scores. The odds they quote for the less probable scorelines eg 3-0 to the weaker team, are nowhere near as long as the true odds.

How do you know what the true odds are?

 

[Interesting Ian]

Any idiot can gamble. To have an edge, it helps to be able to analyze the game and the odds. To do this, you need some background in math and statistics.

Ian, like it or not, you would benefit from learning some basic math and statistics terminology and concepts. Otherwise, when you try to discuss these concepts with people who are knowledgeable about such things, you won't be able to understand them. Asking them to dumb down what they are saying so you can grasp it is unrealistic - they won't bother.

Innumeracy can be as big a disadvantage as illiteracy.

I'm not asking them to dumb down, I'm simply asking them to communicate in English. Saying that it is absolutely impossible to do so is like me saying it is absolutely impossible for me to communicate philosophical ideas to people.

No I think not. Well . . .maybe to skeptics, but they never seem to understand anything.

And another thing. I suspect people aren't helping me because the question is simply too difficult for them. They're not making any comments on my thoughts because they simply don't know.

People might be educated in mathematics on here, but the problem is that education does not increase ones intelligence.

I think Art's "solution" was nothing of the sort. He seemed to be talking about the probability of a team winning the whole match. That's fine, but I've worked out how to do that now in the past few days.

 

[Interesting Ian]

Innumeracy can be as big a disadvantage as illiteracy.

Yeah right. How come I've outargued people on here regarding mathematics on the few occasions I've cared to talk about it. How come I was vastly better than maths than anyone else at school. How come I got a grade "A" in "O" level maths despite only doing the CSE course.

If one thing is absolutely certain my numerical ability is certainly not in doubt.

Don't attempt to communicate with me. I don't take kindly to talking to morons who insult me.

 

[Interesting Ian]

How do you know what the true odds are?

No one knows what the true odds are. It's not like flipping a coin. We only have estimates as to what the odds are. That makes it that much more difficult to make money.

But there are estimates from people who really know what they're talking about, and they're estimates from people who just go by gut feelings.

 

[Robin]

Now on average 44% of all goals are scored in the 1st half and 56% in the second half.

Note that this appears to be a median value. There are some teams that score about 73% of their goals on average in the second half. This could be highly significant if they are evenly matched against a team who score less than 50% of their goals in the second half on average.

What I need to do is find the probability where:

a1 > b1

AND

a2 > b2

It seems to me that you can’t go from P(a1),P(b1) ,P(a2), P(b2) to P(a1>b1 && a2>b2) directly. I would have thought that you would have to derive that last probability independently.

This gels with the psychology of sports where you might expect that some teams will regard a half time defeat as a discouragement while others will regard it as a challenge. In other words it is not just the raw scores that might influence a change in lead.

By a fairly rough crunching of 5 years of English premier league data seems to suggest, for example, that Arsenal have come from behind in 5% of their matches, the highest percentage of any team. Tottenham on the other hand have never come from behind during this same period.

So if history is a predictor of future performance in this respect then you might expect that if Arsenal was playing any team capable of beating them then there might be about a 0.05 chance that the lead will change.

Interestingly the teams that on average score high in the second half don’t necessarily win from behind more often. If fact it appears to be the teams that have the closest to a 50/50 split between first half and second half goals that win from behind more often.

Unless I email Betfair, maybe they'll know how to calculate it . . .hmmm

Or perhaps on the other hand they don’t calculate it – the odds might simply reflect the bets that have been placed. As I said before this is often the strongest predictor there is.

 

[Art Vandelay]

This gels with the psychology of sports

That should be "jell".

By a fairly rough crunching of 5 years of English premier league data seems to suggest, for example, that Arsenal have come from behind in 5% of their matches, the highest percentage of any team. Tottenham on the other hand have never come from behind during this same period.

Is that the number of times they came from behind, divided by the total number of games, or the number of times they come from behind, divided by the number of games in which they initially behind?

Will you please stop trying to show off??

Wow, you sure are self-centered, aren't you? If I have a conversation with a completely different person, it must be because I'm trying to show off in front of you.

Why don't you try engaging in a philosophical argument? You know, just so I can take the p*ss out of you.

I'm been trying to look past your boorish behavior and help you anyway, but I've had enough.

I'm not asking them to dumb down, I'm simply asking them to communicate in English.

I am communicating in English. I'm just using terms that you're not familiar with. Because you're too lazy to look them up.

Saying that it is absolutely impossible to do so is like me saying it is absolutely impossible for me to communicate philosophical ideas to people.

People discussing philosophy quite often use esoteric words, or common words in uncommon ways. They do this because they're discussing concepts that can't easily be expressed in common English. And from I've seen, it does seem very difficult for you to communicate philosophical ideas.

And another thing. I suspect people aren't helping me because the question is simply too difficult for them.

Yeah, that's right. You're so smart, and everyone else is an idiot.

Yeah right. How come I've outargued people on here regarding mathematics on the few occasions I've cared to talk about it.

As judged, of course, by you.

f one thing is absolutely certain my numerical ability is certainly not in doubt.

On the contrary, you have shown yourself to be unfamiliar with even the most basic mathematical concepts.

Don't attempt to communicate with me. I don't take kindly to talking to morons who insult me.

Pot, kettle.

 

[Interesting Ian]

Note that this appears to be a median value. There are some teams that score about 73% of their goals on average in the second half.

This could be highly significant if they are evenly matched against a team who score less than 50% of their goals in the second half on average.

Over a few years or over 1 season? The players constituting a team change significantly from season to season so it wouldn't be meaningful to talk about the same team over a number of years. But if you're only talking about 1 season then the 73% wouldn't be a reliable average figure. (and an average figure of 73% over many years is simply unbelievable).

What I'll do when the new season starts is to assume the 44%:56% figure for each team but modify it as the results come in. Thus if after 25 games a team scores 73% of their goals in the 2nd half then my estimation for the percentage of goals they will score in 2nd half might go up to something like 64%. The long term average should have more influence for future matches than something like the last 25 games.

This gels with the psychology of sports where you might expect that some teams will regard a half time defeat as a discouragement while others will regard it as a challenge. In other words it is not just the raw scores that might influence a change in lead.

By a fairly rough crunching of 5 years of English premier league data seems to suggest, for example, that Arsenal have come from behind in 5% of their matches, the highest percentage of any team. Tottenham on the other hand have never come from behind during this same period.

So if history is a predictor of future performance in this respect then you might expect that if Arsenal was playing any team capable of beating them then there might be about a 0.05 chance that the lead will change.

Interestingly the teams that on average score high in the second half don’t necessarily win from behind more often. If fact it appears to be the teams that have the closest to a 50/50 split between first half and second half goals that win from behind more often.

Of course we can take all sorts of factors into account, but I'm not sure if it's really worth the time analysing all this. People on Betfair can bet on the half-time result (win, lose or draw). And one or two bookies offer odds on the half-time market. So to work out the probability for half time/full time market it's a good idea to use these probabilities quoted for half time (as well as expected total goal averages for both teams at full-time), as obviously they will take account of the factors you have mentioned (or at least the bookies figures will). I don't have the average goal expectation for half-time. However, knowing the odds for the half time result, and knowing the total goal expectations for each team at full time should give me sufficient information to calculate the goal superiority at half time.

And come to think of it, it is this goal superiority which is the crucial factor here. All I need to know is the goal superiority for both half-time and full-time. Hmmm . . . in which case forget about when I said I'll use the 44:56 ratio and modify as new results come in.

Or perhaps on the other hand they don’t calculate it – the odds might simply reflect the bets that have been placed. As I said before this is often the strongest predictor there is.

It would be if there were a sufficient number of bets placed on the half-time/full time market. I'm far from sure that is the case. Even for the straightforward match odds the odds can differ significantly from day to day. At what point are the true odds given? 2 days before the match? Just before the match?

And there definitely seems to be a bias towards the favourites. I mean a greater bias than is warranted.

Anyway, I'm leaving the half-time full-time figure until the new season starting in August (or perhaps for the world cup).

I did try dabbling in it a few days ago with the intent to sell again when the odds shortened. I couldn't work out the odds myself -- as people know this is what I'm currently trying to figure out. But I reckoned that if I compare the odds at betfair with that given by the bookies, then the odds for those outcomes at Betfair which are significantly higher than the bookies will be highly likely to drift back in as the match approaches.

Er . .it didn't work. Most of the odds drifted out even further! This rarely happens to me in the correct score market. So I really need to try and get some idea of the probabilities for this half time/full time market before placing any further bets on it.

Past 2 weeks has been pretty good in terms of profits. About £190 made which is pretty good considering I only make very small bets and never let bets ride but always sell them again.

 

[Robin]

That should be "jell".

Oh for crying out loud. Anyway, not necessarily - the 'gel' spelling for the verb form is an older one and at least the Encarta dictionary agrees with me:

2. take form: to take on a definite form ( informal )
The idea didn't begin to gel until I'd gotten home.

Or to put it another way there is no correct spelling for informal usage.

Is that the number of times they came from behind, divided by the total number of games, or the number of times they come from behind, divided by the number of games in which they initially behind?

That should be "... the number of times they came from behind, divided by the number of games in which they were initially behind". In any case it is the first, you cannot make bets once the game has started.

I am communicating in English. I'm just using terms that you're not familiar with.

"I'm using terms with which you are not familiar"

And from I've seen, it does seem very difficult for you to communicate philosophical ideas.

"And from what I've seen ..."

 

[Art Vandelay]

Oh for crying out loud. Anyway, not necessarily - the 'gel' spelling for the verb form is an older one and at least the Encarta dictionary agrees with me:

I didn't mean to offend you. I just thought I should inform you. I guess I ended up being the one who learned something. Note that I didn't mention things like "If fact it appears to be the teams that have the closest to a 50/50 split between first half and second half goals that win from behind more often" that are clearly typos.

In any case it is the first, you cannot make bets once the game has started.

II seems to be saying that you can.