Help needed with Excel.

[brodski]

Gosh that was funny. I'm splitting my sides laughing. .

It was funny yes, but clearly not meant in a malicious way, it was just a silly little joke.

I fail to see what is amusing about arseholes ruining this thread.

I suspected it might be too good to last. There's always some people who endeavour to piss me off as much as possible.

Damn shame.

Larsens comment was completely uncalled for, but LLHs comment was just a little gentle ribbing, get over it.

 

[Molinaro]

LLH is the one who needs to 'get over it'. It's all fine and dandy to form an opinion about someone. However, when that opinion leaves you unable to differentiate between valid questions or discussions and nonsense, then it is you who should be shuned as much as any woo.

The pettiness of the so-called enlightened one's on these forums is quite pathetic.

 

[LordoftheLeftHand]

Whether his comment is serious or not, I have absolutely no idea what he means. I have never encountered the word "floobie". Even if I had I cannot imagine that it would make sense in the context of what he wrote. It's just words strung together meaninglessly so far as I can ascertain. So what's the joke?

You are right that is all it was. I saw you were having a serious conversation and I couldn't resist derailing it with random nonsense. Sorry.

LLH

 

[Interesting Ian]

You are right that is all it was. I saw you were having a serious conversation and I couldn't resist derailing it with random nonsense. Sorry.

LLH

I'm sorry too

Sometimes I get ridiculously bad tempered over relatively trivial things. I was just a bit touchy after Larsen's unkind words.

 

[aggle-rithm]

I'm sorry too

Sometimes I get ridiculously bad tempered over relatively trivial things. I was just a bit touchy after Larsen's unkind words.

Same here. I confess I hadn't actually read Ian's post thoroughly before spewing my drink at the response. Something about the use of whimsical emoticons in the middle of a serious discussion of mathematics and computer software just seemed to be begging for a crass response...

 

[LordoftheLeftHand]

Same here. I confess I hadn't actually read Ian's post thoroughly before spewing my drink at the response. Something about the use of whimsical emoticons in the middle of a serious discussion of mathematics and computer software just seemed to be begging for a crass response...

Oh I read it before I posted, I just couldn't resist the urge to turn the tables on him (even though it was kind of childish).

LLH

 

[Lucky]

Ian, I’m baffled by your account of the soccer match data, for 2 reasons:

1) Your Y-values aren’t probabilities (and they go the wrong way).

2) Whatever they are, I suppose the Y-values were derived from the X-values, so I don’t understand why you want to find the relation by regression analysis.

You can’t re-create a defined relationship from the numbers, unless you know the form of the equation and just want to calculate the coefficients (and even then you will have rounding errors).

Please give more details!

 

[alfaniner]

Lucky, before you waste any more time, read this...

...I know virtually nothing about mathematics.

...

 

[Interesting Ian]

Ian, I’m baffled by your account of the soccer match data, for 2 reasons:

1) Your Y-values aren’t probabilities (and they go the wrong way).

They are probabilities. Perhaps it would help if I pasted in a couple of the tables here.

(Oops, just finished my post and discovered that I'm unable to paste in properly. Why the hell can't we paste something simple like a table!)

HOW MANY GOALS WILL THERE BE?

PRE-MATCH EXPECTATION: 2.2

So far 0 So far 1 So far 2

MINUTE Under 2.5 Over 2.5 Under 2.5 Over 2.5 Under 2.5 Over 2.5

1.00 1.60 2.66 - - - -
6.00 1.55 2.83 2.64 1.61 7.95 1.14
11.00 1.49 3.05 2.48 1.68 7.25 1.16
16.00 1.43 3.33 2.32 1.76 6.57 1.18
21.00 1.37 3.67 2.17 1.86 5.94 1.20
26.00 1.32 4.10 2.02 1.98 5.35 1.23
31.00 1.27 4.65 1.89 2.12 4.81 1.26
36.00 1.23 5.35 1.77 2.31 4.31 1.30
41.00 1.19 6.28 1.65 2.54 3.86 1.35
46.00 1.15 7.86 1.53 2.90 3.37 1.42
51.00 1.12 9.70 1.44 3.30 3.01 1.50
56.00 1.09 12.60 1.35 3.88 2.67 1.60
61.00 1.06 17.34 1.27 4.75 2.36 1.74
66.00 1.04 25.73 1.20 6.12 2.07 1.93
71.00 1.02 42.35 1.13 8.52 1.82 2.22
76.00 1.01 81.58 1.08 13.37 1.59 2.68
81.00 1.00 205.11 1.04 26.21 1.39 3.55
86.00 1.00 895.32 1.01 90.78 1.21 5.66



PRE-MATCH EXPECTATION: 3.2

So far 0 So far 1 So far 2

MINUTE Under 2.5 Over 2.5 Under 2.5 Over 2.5 Under 2.5 Over 2.5

1.00 2.61 1.62 - - - -
6.00 2.44 1.69 5.24 1.24 20.13 1.05
11.00 2.27 1.79 4.73 1.27 17.61 1.06
16.00 2.11 1.90 4.25 1.31 15.29 1.07
21.00 1.96 2.04 3.81 1.36 13.20 1.08
26.00 1.82 2.22 3.40 1.42 11.34 1.10
31.00 1.69 2.45 3.04 1.49 9.71 1.11
36.00 1.57 2.74 2.72 1.58 8.29 1.14
41.00 1.47 3.12 2.43 1.70 7.06 1.16
46.00 1.36 3.76 2.13 1.88 5.81 1.21
51.00 1.29 4.49 1.92 2.08 4.94 1.25
56.00 1.22 5.63 1.73 2.38 4.15 1.32
61.00 1.15 7.47 1.55 2.81 3.47 1.41
66.00 1.10 10.65 1.40 3.49 2.88 1.53
71.00 1.06 16.83 1.27 4.66 2.38 1.72
76.00 1.03 31.11 1.17 6.94 1.97 2.03
81.00 1.01 75.15 1.09 12.63 1.62 2.62
86.00 1.00 317.69 1.03 36.85 1.33 4.06



I have 6 such tables which I copied from a book (the definitive guide to betting exchanges). These are the probabilities that the author reckons hold for there either being less or more than 2.5 goals in a football (soccer) match as the match progresses. It lists the probabilities for there being so far 0, 1 or 2 goals. The 6 tables represent the pre-match expectation of 2.2, 2.4, 2.6, 2.8, 3.0, and 3.2 Goals of which I've just pasted in the first and last. The figures I quoted in my opening post are from the first minute (i.e right at the beginning of the match) for there being under 2.5 goals as the pre-match expectation of goals increases from 2.2 to 3.2 goals. Now the greater number of goals we expect on average before the match, the less probable that there will be less than 2.5 goals. That is why y decreases.

What I'm going to do is to have just one table, and when I enter the pre-match goal expectation in a cell it will tell me all the probabilities for getting less or more than 2.5 goals as the match progresses. I needed to find the relationship between the probability for under and over 2.5 goals for each 5 minute interval in the game as the pre-match goal expectation increases. That way I can enter any value into the cell eg a pre-match expectation of 2.7 goals, and obtain all the appropriate probabilities (the mathematical relationship I obtained told me the probability for a table generated by inputting a pre-match goal expectation wasn't simply half way between the values in a 2.6 and 2.8 tables).

I also intend to generate tables for the probabilities for there being under and over 1.5 goals as the match progresses, and the same goes for under and over 3.5 goals, under and over 4.5 goals, under and over 5.5 goals and under and over 6.5 goals.

Why am I doing all this? I expect everyone has guessed. It is for the purposes of gambling on the total number of goals in football matches whilst a match progresses.

2) Whatever they are, I suppose the Y-values were derived from the X-values, so I don’t understand why you want to find the relation by regression analysis.

Regression analysis?? What's that? The thing is I don't know where the guy who produced the tables got his figures from. He simply says this is what he reckons the probabilities are. Actually I did discover yesterday where he got the figures from for the probabilities before the match, or in the first minute. They are obtained from applying a poisson distribution to the average pre-match expected goal total. (I don't know what a poisson distribution is, but I don't need to because excel has it built in!). However I'm unable to determine why the probabilities diminish and increase the way they do as teh match progresses. I would have thought that the probabilities would decrease and increase uniformly (linearly?). But they don't.

You can’t re-create a defined relationship from the numbers, unless you know the form of the equation and just want to calculate the coefficients (and even then you will have rounding errors).

Please give more details!

You'll need to speak in English. I don't understand what co-efficients mean.

Anyway I've provide much more detail, so hopefully you should understand what I'm doing.

 

[Interesting Ian]

Lucky, before you waste any more time, read this...

Originally Posted by Interesting Ian :
...I know virtually nothing about mathematics.

What little mathematics I've done I was much better at than any of my fellow pupils at school. Indeed in my 4th year exam when I was 14 I got the highest mark ever in the history of the school. But I never did any more mathematics after 16, and I've never done any statistics whatsoever (and certainly have never done any calculus and the like).

 

[Interesting Ian]

Ian, I’m baffled by your account of the soccer match data, for 2 reasons:

1) Your Y-values aren’t probabilities (and they go the wrong way).

Why are you saying the y values aren't probabilities? I'm using decimal notation. 1.6 means that there is a 100/1.6 percent of a chance of the event happening i.e 62.5%

 

[I less than three logic]

Why are you saying the y values aren't probabilities? I'm using decimal notation. 1.6 means that there is a 100/1.6 percent of a chance of the event happening i.e 62.5%

In statistics, the requirements of probability are:

1. The probability of any outcome must lie between zero and one.
2. The sum of the probabilities of all the outcomes in a sample must be one.

So the 1.6 you had, being equal to 62.5%, should be written as .625 if you want to use it as a statistical probability. Also, if you add each of your Ys together as statistical probabilities then you are well over 1, and this means that you can’t use your set of Ys as statistical probabilities.

However, this does invalidate what you are attempting to accomplish, I just think you have your labels wrong. You can still use your data as graph plots and uses statistics to measure the linear relationship. You have to remember though, that once you start using statistical inference, you’ll no longer be able to get exact answers.

In statistics, when you have a scatter plot graph, like the one your data makes, you can use the data to make the least squares line. This is a line drawn through the points so that the sum of the squared deviations between the points and the line is minimized.

This line won’t give you exact answers though; just ones close to the actual answer within reasonable probability. Imagine that on every point of that line there is a bell curve that represents the probably of the actual point being a certain distance from your line.

I calculated such a line here for you.

I did some quick calculations for you.

I came up with a correlation coefficient of 0.9947 which made my r^2 = 0.98942809.

The least-squares line I came up with was y = 1.0071x - 0.6609

Although, this line didn’t produce the exact values like you asked for, but working with statistics this is expected. The value for x = 3.2 returned y = 2.56182 which is a little closer than the 2.82 you had earlier. The closest value was for x = 2.4, which gave me y = 1.75614.

… Hmm, seems that while I was working the problem the old fashion way I learned in statistics class (some paper and a calculator), some of you have already produced some better answers for him.

 

[Art Vandelay]

I don't believe it! I just spent the last hour trying to figure it out, and adding more digits for "e" and right clicking the equation and expanding the digits after the decimal points there, and all the time I had neglected to put those brackets in!

Really? That was the first thing I thought of. Whenever things don't come out right, parantheses are one of the first things I check.

BTW is it not possible just to put e directly in rather than some approximation like 2.71828?

exp(1)=e
pi()=pi

The pi function is what's known as a "null function"; it only has one value, so you don't have to enter any input. Other Excel null functions include:

today's date: today()
date and time: now()
random number generator: rand()

A shorter exponential regression equation would simply calculate e ^ 0.49 and replace it to result in y = 1.6323 ^ x = (1 + 0.6323) ^ x.

Shorter, maybe, but not necessarily better. Base e is the canonical form, and it's often a lot easier to work with. Want to take the derivative? Trivial. Multiply two functions together? Just add the exponents. Also, if .49 is an exact value, you're now introducing rounding errors. And if you're working in Excel, you can use the the exp function, rather than ^. Finally, your modification is incorrect: it should be 0.5405*(1.6323 ^ x). Or (1.6323 ^ (x-1.25566)).

A relationship such as this is not wholly obscure and using base 1.6323 instead of e ^ x form indicates that for every full unit of increase the value of x (e.g., from 2 to 3) the value of y will increase by approximately 63%.

Yeah, but if we're dealing with interest, the e^(0.49x) indicates 49% interest compounded continuously. In other words, for small h, there's an increase of about 49h%.

Whether or not your data or results are applicable at values previous or post your recorded data

That should be "values previous to or after your recorded data". And the rest of your sentence is rather convoluted. Not to be too critical, but I don't think that there's much chance that anyone will understand what you were saying unless they already understand interpolation and extrapolation.

CFLarsen, I have great respect from what you've written and I use skepticreport a lot. But cool down for a moment.

Yeah, he's one of those people who can post intelligent remarks one day, and batsh*t crazy remarks another.

Who's comment?

Whose.

Mine or his? Whether his comment is serious or not, I have absolutely no idea what he means.

Yeah, it's kinda an in-joke in some circles. Especially Star Trek fans. E.g. "Have you tried modulating the phase variance?" (There's a cheat code for StarCraft that refers to this type of joke).

LLH is the one who needs to 'get over it'. It's all fine and dandy to form an opinion about someone. However, when that opinion leaves you unable to differentiate between valid questions or discussions and nonsense, then it is you who should be shuned as much as any woo.

Huh?

The pettiness of the so-called enlightened one's on these forums is quite pathetic.

At the risk of being petty, that should be "ones".

2) Whatever they are, I suppose the Y-values were derived from the X-values, so I don’t understand why you want to find the relation by regression analysis.

Huh?

You can’t re-create a defined relationship from the numbers, unless you know the form of the equation and just want to calculate the coefficients (and even then you will have rounding errors).

A finite set of points cannot differentiate between functions with arbitrarily large numbers of parameters, but if you know what assumptions to make, you can narrow your space down to a manageable number of parameters. With a correlation coefficient of .99 and only two parameters, you can be pretty sure you've guessed correctly.

(Oops, just finished my post and discovered that I'm unable to paste in properly. Why the hell can't we paste something simple like a table!)

Are you trying to post from Excel or Winword?

That way I can enter any value into the cell eg a pre-match expectation of 2.7 goals, and obtain all the appropriate probabilities (the mathematical relationship I obtained told me the probability for a table generated by inputting a pre-match goal expectation wasn't simply half way between the values in a 2.6 and 2.8 tables).

Something to think about: is the probability at halftime for expected=2.6 the same as the probability at the end of a game for expected=1.3? Why or why not?

Why am I doing all this? I expect everyone has guessed. It is for the purposes of gambling on the total number of goals in football matches whilst a match progresses.

Keep in mind that your bookie almost certainly already has all of this and more, so this should only be done for your amusement. You're not going to be outsmarting professionals with this.

Regression analysis?? What's that?

It's the stuff you had Excel do.

However I'm unable to determine why the probabilities diminish and increase the way they do as teh match progresses. I would have thought that the probabilities would decrease and increase uniformly (linearly?). But they don't.

Suppose there's a probability p of scoring at least once within a five minute period. If the team has already scored twice, all you care about is whether they score once more. Scoring two more goals, three more goals, etc. isn't any different to you, is it? So in a ten minute period, there are four possibilities:
score nothing ((1-p)^2)
score in first five minute period, but not in second (p(1-p))
score in second five minute period, but not in first ((p-1)p)
score in both (p^2)

So the total probability is the sum of the last three:
p((2-2p)+p)=p(2-p)

Notice that the last one doesn't count for any more than the middle two; the extra goal(s) are "wasted". You therefore get less than 2p. If you were to count the last one twice because there are twice as many goals, you would get 2p, which is what you (incorrectly) expected.

Furthermore, if it were linear, then there would be a point at which the probability is more than 100%, which is absurd, isn't it?

You'll need to speak in English. I don't understand what co-efficients mean.

A variable is a number that changes. A constant is a number that can't change. When you multiply a variable by a constant, the constant is called a coefficient. So in this case, .49 is a coefficient for x, and .5405 is a coefficient for e^(.49x).

 

[Interesting Ian]

Originally Posted by Interesting Ian :
Why are you saying the y values aren't probabilities? I'm using decimal notation. 1.6 means that there is a 100/1.6 percent of a chance of the event happening i.e 62.5%

I less than logic said:
In statistics, the requirements of probability are:

1. The probability of any outcome must lie between zero and one.
2. The sum of the probabilities of all the outcomes in a sample must be one.

So the 1.6 you had, being equal to 62.5%, should be written as .625 if you want to use it as a statistical probability.

Or 100 if you're working in percentages.

Also, if you add each of your Ys together as statistical probabilities then you are well over 1, and this means that you can’t use your set of Ys as statistical probabilities.

Let me make myself more clear. The y values I originally quoted were 1.6, 1.75, 2.6, 2.12, 2.35, 2.61. These are the estimated true odds expressed in decimal format of getting less than 2.5 goals (i,e 0, 1 or 2 goals) right at the start of the match where the pre-match average goal expectation is 2.2, 2.4, 2.6, 2.8. 3.0, 3.2 respectively. Dividing each of these figures into 100 will give the probability in percentage form. But clearly they should not add up to 100! We are considering differing matches with differing pre-match expectations of goals!

No, what should add up to 100% is that for any given pre-match expectation of goals the respective probabilities for there being under 2.5 goals and over 2.5 goals, for any given number of minutes into the game, should add up to 100.

Thus let's look at the 2.2 goals table. At minute one the figures I have are 1.60 for under 2.5 goals and 2.66 for over 2.5 goals. So dividing each of those figures into 100 and adding them together should equal 1.

In statistics, when you have a scatter plot graph, like the one your data makes, you can use the data to make the least squares line. This is a line drawn through the points so that the sum of the squared deviations between the points and the line is minimized.

Yer wha . . .. I guess you're talking about this R squared. I guessed that the closer to 1 that figure is the better fit achieved.

This line won’t give you exact answers though; just ones close to the actual answer within reasonable probability. Imagine that on every point of that line there is a bell curve that represents the probably of the actual point being a certain distance from your line.

I calculated such a line here for you.


Originally Posted by I less than three logic :
I did some quick calculations for you.

I came up with a correlation coefficient of 0.9947 which made my r^2 = 0.98942809.

The least-squares line I came up with was y = 1.0071x - 0.6609

Although, this line didn’t produce the exact values like you asked for, but working with statistics this is expected. The value for x = 3.2 returned y = 2.56182 which is a little closer than the 2.82 you had earlier. The closest value was for x = 2.4, which gave me y = 1.75614.

.

I've done all this. I got the exact value 2.62. I only got 2.82 initially because I didn't realise one had to enclose the exponential value in brackets.

We're only talking about one line here i.e how the probability changes for scoring under 2.5 goals at minute 1 as the pre-match goal expectation varies. I have to do this for 5 mins, 10 mins, up to 85 mins. Then I've got to do it for over 2.5 goals. Then I've got to repeat all that for figuring the over and under 1.5 goal markets, the over and under 3.5, 4.5, 5.5 and 6.5 markets. So a huge amount of work.

But I can do it all now. The only problem I'm facing is that the probabilities when I get to 65 mins are insufficiently accurate. But I can work my way around that.

 

[Interesting Ian]

Originally Posted by Interesting Ian :
I don't believe it! I just spent the last hour trying to figure it out, and adding more digits for "e" and right clicking the equation and expanding the digits after the decimal points there, and all the time I had neglected to put those brackets in!

Art Vandelay
Really? That was the first thing I thought of. Whenever things don't come out right, parantheses are one of the first things I check.

I haven't done any maths in over 20 years.

BTW is it not possible just to put e directly in rather than some approximation like 2.71828?

exp(1)=e
pi()=pi

The pi function is what's known as a "null function"; it only has one value, so you don't have to enter any input. Other Excel null functions include:

today's date: today()
date and time: now()
random number generator: rand()

A random number! I think not! It will be one of these pseudo-random numbers which mathematicians like to call "random". Quite the opposite to real random numbers!

Originally Posted by Math Maniac :
Whether or not your data or results are applicable at values previous or post your recorded data

Art Vandelay
That should be "values previous to or after your recorded data". And the rest of your sentence is rather convoluted. Not to be too critical, but I don't think that there's much chance that anyone will understand what you were saying unless they already understand interpolation and extrapolation.

I don't understand them, but I understood what he was saying perfectly. I'm assuming that all the values in between 2.2 and 3.2 will exhibit the same mathematical relationship. In practice the average goal expectation will never dip below 1.9, and never exceed 3.4. So the figures should be ok (and apart from that I discovered the initial figures at minute 1 are simply obtained by a poisson distribution)

Originally Posted by Interesting Ian :
Who's comment?

Art Vandelay
Whose.

I said "who's"??

Mine or his? Whether his comment is serious or not, I have absolutely no idea what he means.

Yeah, it's kinda an in-joke in some circles. Especially Star Trek fans.

I have only ever seen the 60's series.

Originally Posted by Interesting Ian :
(Oops, just finished my post and discovered that I'm unable to paste in properly. Why the hell can't we paste something simple like a table!)

Art Vandelay
Are you trying to post from Excel or Winword?

What's "winword"? Microsoft word? I tried from excel initially. Then pasted into word and copied the html source. It complained I had 511 images when I'm only allowed 10, so I switched off smilies. Still came out wrong. So I pasted into notepad and then pasted from that. Couldn't be bothered to work it out, it was about 3am in the morning!

II
That way I can enter any value into the cell eg a pre-match expectation of 2.7 goals, and obtain all the appropriate probabilities (the mathematical relationship I obtained told me the probability for a table generated by inputting a pre-match goal expectation wasn't simply half way between the values in a 2.6 and 2.8 tables).

Art Vandelay
Something to think about: is the probability at halftime for expected=2.6 the same as the probability at the end of a game for expected=1.3? Why or why not?

No it's not which makes it a bit more complex.

Here are the figures for getting less than 2.5 goals with a pre-match expectation of average of 2.2 goals:


62.5% FT 87% HT

(these figures of course assume no goal is scored in the first half. Otherwise the probabilities would be very different!)

So if no goal is scored come half time, the probability that the total number of goals scored will be less than 2.5 goals will increase from 62.5% at the beginning of the match, to 87% at half time.

The figure I get from applying the poisson distribution to 1.1 for whole game is 80.1%. So that's 7% less.

This might seem a bit strange because if the pre-match expectation is 2.2, and goals and the chance of a goal being scored doesn't vary as the match progresses, then the expectation at half time should also be 80.1%.


I suppose this is just the difficulty of trying to mathematically model human behaviour. At the beginning of the match for example, maybe the 2 teams tend to test each other out and less sustained effort is made to score. And maybe near the end more attacking efforts are made. In which case the chances of scoring varies as a match progresses. Anyone have figures on this?

Why am I doing all this? I expect everyone has guessed. It is for the purposes of gambling on the total number of goals in football matches whilst a match progresses.

Art
Keep in mind that your bookie almost certainly already has all of this and more, so this should only be done for your amusement. You're not going to be outsmarting professionals with this.

I'm not betting against bookies. It's p2p betting (www.betfair.com). Imagine you're just about to watch a football match in a pub. You reckon there's about 55% of a chance there will be more than 2.5 goals. A person you struck up a conversation with reckons there's 55% of a chance there will be less than 2.5 goals. So you make a bet with him. If there is more than 2.5 goals (3, 4 5 goals etc), then he will pay you £10. If there's less than 2.5 goals (0.1 or 2 goals), you will pay him £10.

But hang on a sec. He's a complete stranger. You don't trust him to pay up should you win. And he doesn't trust you. So when you strike up the bet each of you give the £10 you could lose to the barman. He looks after the money until the match finishes then gives the the person who wins his original £10 back plus his winnings. But the winnings will be slightly less than £10 because the bar man wants to be paid for his bother. In fact he normally takes 5% of the winnings. So the person who wins only gets a total of £19.50 back (his original £10 but £9.50 winnings). This is exactly how betfair works except you can bet against anyone in the world.

But in the total goals market at betfair the commision paid is only 1% rather than 5%. That makes it much more likely you can be up over the long haul.

So I'm not competing against professionals generally. Indeed I'm getting the pre-match goal expectations from the bookies themselves (the spread firms).



Shall address rest of your post later.

 

[Interesting Ian]

However I'm unable to determine why the probabilities diminish and increase the way they do as teh match progresses. I would have thought that the probabilities would decrease and increase uniformly (linearly?). But they don't.

Suppose there's a probability p of scoring at least once within a five minute period. If the team has already scored twice, all you care about is whether they score once more. Scoring two more goals, three more goals, etc. isn't any different to you, is it? So in a ten minute period, there are four possibilities:
score nothing ((1-p)^2)
score in first five minute period, but not in second (p(1-p))
score in second five minute period, but not in first ((p-1)p)
score in both (p^2)

So the total probability is the sum of the last three:
p((2-2p)+p)=p(2-p)

Notice that the last one doesn't count for any more than the middle two; the extra goal(s) are "wasted". You therefore get less than 2p. If you were to count the last one twice because there are twice as many goals, you would get 2p, which is what you (incorrectly) expected.

Furthermore, if it were linear, then there would be a point at which the probability is more than 100%, which is absurd, isn't it?

Yes the probabilities can't decrease uniformly. I'm not thinking there.

I had in mind that the average number of goals in any remaining period should decrease uniformly. So with pre-match expectation of 2.2 goals, the half time expectation with no goals scored so far would be half of that. But if I enter 1.1 and do a poisson distribution, the figures don't tally.

Have to think about this.

 

[Interesting Ian]

How do you link cells both ways? In other words changing the value in cell A so that it will automatically change the value in cell B to the same value, and also changing the value in cell B so that it will change the value in cell A to the same value?

Another question. Is it possible to keep changing a value in a cell so that after every 5 minute interval after a specific time that I can specify, the cell will display the value of a succession of differing cells?

 

[brodski]

How do you link cells both ways? In other words changing the value in cell A so that it will automatically change the value in cell B to the same value, and also changing the value in cell B so that it will change the value in cell A to the same value?

I don't think you can, that would be circular reference, because (fr instance) if b=a=b you will never get an answers unless you define either "a" or "b".
I don't think I'm explaining myself to clearly here.
Right, if excel need a value of A to determine a value of B, but needs a value of B to determine a value of A, it cannot define either, as the equation will keep looping back on itself.

ETA- if you can be clear about exactly why you want a=b=a=b etc, maybe I can find a way around it.

 

[Jarom]

I had in mind that the average number of goals in any remaining period should decrease uniformly. So with pre-match expectation of 2.2 goals, the half time expectation with no goals scored so far would be half of that. But if I enter 1.1 and do a poisson distribution, the figures don't tally.

Have to think about this.

Let me tell you a bit about the Poisson distribution; some of which you probably already know. This will help me to explain what went wrong with your reasoning.

The Poisson distribution is a probability distribution over the set of natural numbers, which means it assigns to each natural number a real number intended to represent the probability of that number being chosen. The Poisson distribution is the appropriate model whenever you're trying to determine the probability of a given number of "events" in a given interval subject to the two conditions:

• The probability of an event doesn't depend on where in the interval you are.
• The probability of an event doesn't depend on how long it's been since the last one.

Since these two conditions are approximately true for football goals, it's a good choice here.

"The Poisson distribution" is a bit of a misnomer, it's actually a family of distributions. To specify a particular Poisson distribution, you just need to specify a single parameter, which is usually denoted with the Greek letter lambda. Conveniently, this parameter turns out to be the average of the distribution, so if the average is known, you have your distribution.

If you want to get Excel to calculate a Poisson distribution without using a builtin formula, use this equation:
P(n goals) = exp(-lambda) * lambda^n / n!

At halftime, assuming there's no bias for scoring in one half or the other (maybe not a good assumption, but you're the one with the tables, so you can check), the current expectation is indeed a Poisson distribution with half the parameter of the initial one. But (important point) we can't get the numbers for this new distribution by rescaling the numbers from the old one to half. This is because there's more to the distribution that just the average. When you rescale to half, this has the effect of multiplying the average by half, just as you want. Unfortunately, it only has the effect of multiplying the standard deviation by (1/sqrt(2)). (Trust me on this one). So to get the correct result, you actually need to recalculate the Poisson distribution from scratch using the new parameter.

Well, I was bored, so I made the spreadsheet in Excel. PM me if you'd like it emailed. Take a look at the formulas; I left in a column where lambda is calculated. You should be able to customize it to your liking.

 

[varwoche]

How do you link cells both ways? In other words changing the value in cell A so that it will automatically change the value in cell B to the same value, and also changing the value in cell B so that it will change the value in cell A to the same value?

The Goal Seek feature might be applicable, no pun intended.