• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

Help needed with Excel.

Interesting Ian

Banned
I
Joined
Feb 9, 2004
Messages
7,675
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 :eek: :eek: :eek:

So it seems that it is giving me this highly obscure relationship between y and x :D 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?
 
Interesting Ian said:
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
Click to expand...

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.
 
rats said:
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.
Click to expand...

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).
 
Interesting Ian said:
So it seems that it is giving me this highly obscure relationship between y and x :D 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.
Click to expand...

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:

[latex]e^x = 1 + x + x^2/2! + x^3/3! + x^4/4! + ...[/latex]

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:

[latex]1! = 1, 2! = 1*2, 3! = 1*2*3, 4! = 1*2*3*4,[/latex]

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:

[latex]e^{\pi i} = -1[/latex]
 
Interesting Ian said:
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).
Click to expand...
Hi Ian,

=0.5405*EXP(0.49*Input!B10)

That should work. Good luck and nice job doing all of this from scratch.
 
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!
 
CFLarsen said:
Here's how:

Turn off your computer.

Don't turn it on again. Ever.
Click to expand...

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?
 
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. :D
 
Last edited:
Math Maniac said:
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.
Click to expand...

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 :)
 
Unnamed said:
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?
Click to expand...

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. :)
 
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
 
alfaniner said:
Now that made me laugh. All the funnier as it was obviously a serious comment.
Click to expand...

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?
 
Interesting Ian said:
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?
Click to expand...
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.
 
brodski said:
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.
Click to expand...

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

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.
 
You must log in or register to reply here.

Back
Top Bottom