Forum Index Register Members List Events Mark Forums Read Help

 International Skeptics Forum Help needed with Excel.

 User Name Remember Me? Password

 Welcome to the International Skeptics Forum, where we discuss skepticism, critical thinking, the paranormal and science in a friendly but lively way. You are currently viewing the forum as a guest, which means you are missing out on discussing matters that are of interest to you. Please consider registering so you can gain full use of the forum features and interact with other Members. Registration is simple, fast and free! Click here to register today.
 Tags excel , help

 12th April 2006, 07:25 AM #1 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Help needed with Excel. I only started using spreadsheets a few months ago so very new at this. Also I know virtually nothing about mathematics. Anyway I have thse values whereby y varies with x. I want to find a precise mathematical relationship between x and y so that if I input any x value I get the y value displayed. The values are these (2.2,1.6), (2.4,1.75), (2.6,1.92), (2.8,2.12), (3, 2.35) and (3.2,2.61). In each case x is the first number and y the second. These y values don't simply increase in value unformly but increase at a very slightly increasing rate. So how do I obtain a mathematical expression? equation? relating the two sets of numbers? I tried the chart function thingy last night and obtained a graph of these points. I was hoping that it might supply me with the relationship between these 2 sets of numbers. So I was messing about and discovered something called "add trendline". So I tried that. Experimented and found the expontial trendline gave the best fit. I ticked the R squared value and equation options. I've got on the chart R^2 = 0.999 and y = 0.5405*e^0.49x So it seems that it is giving me this highly obscure relationship between y and x I didn't know what "e" meant, but I have a vague feeling it's simply a number like pi with an infinite number of digits after the decimal point. I looked it up on google and some site said it's value is 2.71828. Unfortunately when I tried it, it gave incorrect values . For example when I put x as 3.2 in the equation it gave an answer of 2.82 when it is supposed to be 2.61 (and 2.82 is way above the trend line). So what gives here?
 12th April 2006, 07:44 AM #2 CFLarsen Penultimate Amazing   Join Date: Aug 2001 Posts: 42,367 Here's how: Turn off your computer. Don't turn it on again. Ever.
 12th April 2006, 07:45 AM #3 rats Muse   Join Date: Mar 2006 Posts: 608 Originally Posted by Interesting Ian For example when I put x as 3.2 in the equation it gave an answer of 2.82 when it is supposed to be 2.61 Here's a quick answer... For a beginner with Excel, sounds like you're doing very well! With y = 3.2, I get x = 2.6. For clarity: y = 0.5405 * e ^ (0.49*x) Good luck, rats.
 12th April 2006, 08:11 AM #4 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by rats Here's a quick answer... For a beginner with Excel, sounds like you're doing very well! With y = 3.2, I get x = 2.6. For clarity: y = 0.5405 * e ^ (0.49*x) Good luck, rats. 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! BTW is it not possible just to put e directly in rather than some approximation like 2.71828? I tried putting =0.5405*e^(0.49*Input!B10) but it just said "name". (Input!B10 is the cell where I put the value of x in).
 12th April 2006, 08:13 AM #5 Ziggurat Penultimate Amazing     Join Date: Jun 2003 Posts: 47,281 Originally Posted by Interesting Ian So it seems that it is giving me this highly obscure relationship between y and x I didn't know what "e" meant, but I have a vague feeling it's simply a number like pi with an infinite number of digits after the decimal point. I looked it up on google and some site said it's value is 2.71828. Yes, it is a special irrational number like pi. In fact, the two happen to be intimately related - you can kind of think of e being the "special" number for exponential functions the same way that pi is the "special" number for trigonomic functions. Here's two little equations which may pique your interest in e: $e^x = 1 + x + x^2/2! + x^3/3! + x^4/4! + ...$ Seems almost too simple, doesn't it? BTW, since you say you don't know much math, the ! notation means factorial. It's defined like this: $1! = 1, 2! = 1*2, 3! = 1*2*3, 4! = 1*2*3*4,$ and so on. Factorials get big very fast. The connection to pi comes from plugging in imaginary numbers to these equations, from which one can prove: $e^{\pi i} = -1$ __________________ "As long as it is admitted that the law may be diverted from its true purpose -- that it may violate property instead of protecting it -- then everyone will want to participate in making the law, either to protect himself against plunder or to use it for plunder. Political questions will always be prejudicial, dominant, and all-absorbing. There will be fighting at the door of the Legislative Palace, and the struggle within will be no less furious." - Bastiat, The Law
 12th April 2006, 08:20 AM #6 Unnamed Thinker   Join Date: Jun 2005 Posts: 230 Originally Posted by Interesting Ian BTW is it not possible just to put e directly in rather than some approximation like 2.71828? I tried putting =0.5405*e^(0.49*Input!B10) but it just said "name". (Input!B10 is the cell where I put the value of x in). Hi Ian, =0.5405*EXP(0.49*Input!B10) That should work. Good luck and nice job doing all of this from scratch.
 12th April 2006, 08:28 AM #7 Math Maniac Thinker     Join Date: Mar 2002 Posts: 161 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. Most people are introduced/farmiliar with this as "Interest = Principal * (1 + r) ^ n" where r is the percentage increase (interest rate per period, when compounded more than once a year). 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%. Whether or not your data or results are applicable at values previous or post your recorded data is another matter as you may only be allowed to interpolate (use the equation for values between your highest and lowest x values) as extrapolation (using your equation to predict values before and after your recorded x values) poses many other questions and concerns and chances for error in approximation. This is probably more detail than necessary but I'm in the library bored and thought I'd attempt to help (I typed this in between teaching classes so hopefully there are not m/any mistakes. Please let me know if you find some. Either way, considering yourself a 'beginner' on Excel and being able to do what you did is EXCELLENT. Good Luck!
 12th April 2006, 08:30 AM #8 Unnamed Thinker   Join Date: Jun 2005 Posts: 230 Originally Posted by CFLarsen Here's how: Turn off your computer. Don't turn it on again. Ever. CFLarsen, I have great respect from what you've written and I use skepticreport a lot. But cool down for a moment. Ignore the name "Interesting Ian". Is there a problem with the post itself?
 12th April 2006, 08:34 AM #9 I less than three logic Graduate Poster   Join Date: Dec 2005 Posts: 1,463 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. __________________ “There is perhaps no better demonstration of the folly of human conceits than this distant image of our tiny world.” - Carl Sagan “The fact that we live at the bottom of a deep gravity well, on the surface of a gas covered planet going around a nuclear fireball ninety million miles away and think this to be normal is obviously some indication of how skewed our perspective tends to be.” – Douglas Adams Last edited by I less than three logic; 12th April 2006 at 08:48 AM.
 12th April 2006, 08:53 AM #10 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by Math Maniac Whether or not your data or results are applicable at values previous or post your recorded data is another matter as you may only be allowed to interpolate (use the equation for values between your highest and lowest x values) as extrapolation (using your equation to predict values before and after your recorded x values) poses many other questions and concerns and chances for error in approximation. Well the figures are to do with pre-match probabilities of soccer matches resulting in less than 2.5 goals i.e either 0, 1 or 2 goals. Thus the values 2.2, 2.4 etc represent the average expected number of goals there will be in the match. The values 1.6, 1.75 etc, represent the probabilities. Thus with an expected average of 2.2 goals there is a 1.6 average expected chance that there will be less than 2.5 goals in the entire match. Now, in practice, in professional soccer, the expected average will never dip below 1.9 goals in the match, nor will it exceed 3.4 (but of course there are many 0-0 and 3-2 scorelines etc -- it's the expected average we're talking about here). So I imagine that extrapolating above or below those values to any significant extent will not be applicable
 12th April 2006, 08:59 AM #11 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 And BTW, I much appreciate everyones help! Thanks!
 12th April 2006, 09:08 AM #12 Ripley Twenty-Nine Muse   Join Date: May 2005 Posts: 849 Originally Posted by Unnamed CFLarsen, I have great respect from what you've written and I use skepticreport a lot. But cool down for a moment. Ignore the name "Interesting Ian". Is there a problem with the post itself? You've echoed my thoughts as well.
 12th April 2006, 10:35 AM #13 LordoftheLeftHand Graduate Poster   Join Date: Jun 2005 Posts: 1,188 Quote: Help needed with Excel. It appears that your floobie index is off on the second plateau. Hope this helps. LLH
 12th April 2006, 10:55 AM #14 aggle-rithm Ardent Formulist     Join Date: Jun 2005 Location: Austin, TX Posts: 15,334 Originally Posted by Unnamed CFLarsen, I have great respect from what you've written and I use skepticreport a lot. But cool down for a moment. Ignore the name "Interesting Ian". Is there a problem with the post itself? It must be a programmer thang. We tend not to be a very couth bunch. I just about spit my drink all over my monitor when I read Claus' post. __________________ To understand recursion, you must first understand recursion. Woo's razor: Never attribute to stupidity that which can be adequately explained by aliens.
 12th April 2006, 11:00 AM #15 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by LordoftheLeftHand It appears that your floobie index is off on the second plateau. Hope this helps. LLH Eh? No. I have no idea what you mean.
 12th April 2006, 11:06 AM #16 alfaniner Penultimate Amazing     Join Date: Aug 2001 Posts: 23,676 Originally Posted by Interesting Ian Eh? No. I have no idea what you mean. Now that made me laugh. All the funnier as it was obviously a serious comment. __________________ Science is self-correcting. Woo is self-contradicting.
 12th April 2006, 11:16 AM #17 Molinaro Illuminator     Join Date: Dec 2005 Posts: 4,701 And now a little asside on the number e. 2.718281828459045.. is actualy quite easy to remember once someone points out the pattern. My grade 10 math teacher pointed it out and I have not forgotten yet.. 21 years later!! 2.7 1828 1828 45-90-45 __________________ 100% Cannuck!
 12th April 2006, 11:20 AM #18 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by alfaniner Now that made me laugh. All the funnier as it was obviously a serious comment. Who's comment? Mine or his? 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?
 12th April 2006, 11:23 AM #19 brodski Tea-Time toad     Join Date: Mar 2005 Posts: 15,516 Originally Posted by Interesting Ian Who's comment? Mine or his? 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? The joke was that he was clearly talking nonsense, whilst feigning helpfulness. The "hope this helps" comment was quite funny, but your response just made the joke.
 12th April 2006, 11:33 AM #20 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by brodski The joke was that he was clearly talking nonsense, whilst feigning helpfulness. The "hope this helps" comment was quite funny, but your response just made the joke. Gosh that was funny. I'm splitting my sides laughing. I fail to see what is amusing about ********* 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.
 12th April 2006, 11:37 AM #21 brodski Tea-Time toad     Join Date: Mar 2005 Posts: 15,516 Originally Posted by Interesting Ian 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. Originally Posted by Interesting Ian I fail to see what is amusing about ********* 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.
 12th April 2006, 12:21 PM #22 Molinaro Illuminator     Join Date: Dec 2005 Posts: 4,701 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. __________________ 100% Cannuck!
 12th April 2006, 12:28 PM #23 LordoftheLeftHand Graduate Poster   Join Date: Jun 2005 Posts: 1,188 Originally Posted by Interesting Ian 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
 12th April 2006, 03:40 PM #24 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by LordoftheLeftHand 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.
 12th April 2006, 06:16 PM #25 aggle-rithm Ardent Formulist     Join Date: Jun 2005 Location: Austin, TX Posts: 15,334 Originally Posted by Interesting Ian 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... __________________ To understand recursion, you must first understand recursion. Woo's razor: Never attribute to stupidity that which can be adequately explained by aliens.
 12th April 2006, 11:51 PM #26 LordoftheLeftHand Graduate Poster   Join Date: Jun 2005 Posts: 1,188 Originally Posted by aggle-rithm 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
 14th April 2006, 03:32 PM #27 Lucky Graduate Poster     Join Date: Apr 2004 Location: Yorkshire Posts: 1,180 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!
 14th April 2006, 05:12 PM #28 alfaniner Penultimate Amazing     Join Date: Aug 2001 Posts: 23,676 Lucky, before you waste any more time, read this... Originally Posted by Interesting Ian ...I know virtually nothing about mathematics. ... __________________ Science is self-correcting. Woo is self-contradicting.
 14th April 2006, 07:36 PM #29 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by 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). 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. Quote: 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. Quote: 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.
 14th April 2006, 07:42 PM #30 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by alfaniner 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).
 14th April 2006, 07:56 PM #31 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by 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). 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%
 14th April 2006, 10:33 PM #32 I less than three logic Graduate Poster   Join Date: Dec 2005 Posts: 1,463 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% 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. 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. … 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. __________________ “There is perhaps no better demonstration of the folly of human conceits than this distant image of our tiny world.” - Carl Sagan “The fact that we live at the bottom of a deep gravity well, on the surface of a gas covered planet going around a nuclear fireball ninety million miles away and think this to be normal is obviously some indication of how skewed our perspective tends to be.” – Douglas Adams
 15th April 2006, 12:53 AM #33 Art Vandelay Illuminator   Join Date: May 2004 Posts: 4,787 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! 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. Quote: 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() Originally Posted by Math Maniac 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)). Quote: 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%. Quote: 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. Originally Posted by Unnamed 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. Originally Posted by Interesting Ian Who's comment? Whose. Quote: 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). Originally Posted by 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. Huh? Quote: 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". Originally Posted by Lucky 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? Quote: 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. 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!) Are you trying to post from Excel or Winword? Quote: 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? Quote: 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. Quote: Regression analysis?? What's that? It's the stuff you had Excel do. Quote: 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? Quote: 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).
 15th April 2006, 03:31 AM #34 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Quote: 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. Quote: 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. Quote: 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. Quote: 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.
 15th April 2006, 04:37 AM #35 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Quote: 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. Quote: 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! Quote: 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) Quote: Originally Posted by Interesting Ian : Who's comment? Art Vandelay Whose. I said "who's"?? Quote: 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. Quote: 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! Quote: 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? Quote: 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.
 15th April 2006, 05:08 AM #36 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 Originally Posted by Art Vandelay 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.
 16th April 2006, 05:32 AM #37 Interesting Ian Banned   Join Date: Feb 2004 Posts: 7,675 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?
 16th April 2006, 08:09 AM #38 brodski Tea-Time toad     Join Date: Mar 2005 Posts: 15,516 Originally Posted by 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? 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.
 16th April 2006, 08:18 AM #39 Jarom Scholar   Join Date: May 2003 Posts: 61 Originally Posted by Interesting Ian 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.
 16th April 2006, 09:13 AM #40 varwoche Penultimate Amazing     Join Date: Feb 2004 Location: Puget Sound Posts: 15,156 Originally Posted by 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? The Goal Seek feature might be applicable, no pun intended. __________________ To survive election season on a skeptics forum, one must understand Hymie-the-Robot. My authority is total - Trump

International Skeptics Forum

 Bookmarks Digg del.icio.us Google Reddit

 Thread Tools

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Welcome to ISF     Welcome!     International Skeptics     Other Skeptical Organizations         JREF     Skeptical Events         TAM Scholarship Auction Reference     The Repository         Forum Newsletters     Book Reviews     Forum Spotlight General Topics     General Skepticism and The Paranormal     Science, Mathematics, Medicine, and Technology     Education     Economics, Business and Finance     History, Literature, and the Arts     Religion and Philosophy     Conspiracies and Conspiracy Theories         9/11 Conspiracy Theories     USA Politics     Non-USA & General Politics     Social Issues & Current Events     Trials and Errors     Computers and the Internet     Conjuror's Corner Members Only     Forum Community         In memoriam...     Humor     Hobbies, DIY and Interests     Movies, TV, Music, Computer Gaming, and other Entertainment     Puzzles     Sports     Archive         Old TAM Auction Threads

All times are GMT -7. The time now is 10:46 PM.
Powered by vBulletin. Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

This forum began as part of the James Randi Education Foundation (JREF). However, the forum now exists as
an independent entity with no affiliation with or endorsement by the JREF, including the section in reference to "JREF" topics.

Disclaimer: Messages posted in the Forum are solely the opinion of their authors.
 -- ISF Blue ---- Aqua ---- Drab Olive ---- Dull Day ---- Eco ---- Purple Haze ---- Nobby's classy style ---- The Red One ---- The Blues ---- Vimto ---- Mobile Contact Us - International Skeptics - Archive - Privacy Statement - Top