Find a formula for serial data

**dendricala** · May 2nd 2011, 07:07 AM

Hello
first time here, and to be honest i m here because i need help.
I have a data serie :
100 0.12
200 0.25
300 0.42
400 0.68
500 1.08
600 1.5
700 1.83
800 2.12
900 2.51
1000 3.09
1100 3.72
1200 4.2
1300 4.64
1400 5.21
1500 6.07
1600 6.99
1700 7.71
1800 8.35
1900 9.21
2000 10.48
2100 11.85
2200 12.92
2300 13.87
2400 15.13
2500 17.02
2750 21.31
3000 26.69
3250 33.07
3500 41.03
3750 50.51
4000 61.96
4250 75.71
4500 92.18
4750 111.9
5000 135.46
5250 163.58
5500 197.06
5750 236.93
6000 280.42
6250 334.76
6500 396.85
6750 467.43
7000 547.32
7250 637.31
7500 738.28
7750 873.75
8000 1004.9
8250 1142.96
8500 1289.66
8750 1442.97
9000 1604.53
9250 1772.41
9500 1948.16
9750 2129.93
10000 2319.2
10250 2514.19
10500 2716.29
10750 2923.82
11000 3138.08
11250 3357.48
11500 3583.23
11750 3813.81
12000 4050.37
12250 4291.47
12500 4538.16
12750 4789.1
13000 5045.25
13250 5305.34
13500 5570.27
13750 5838.85
14000 6111.88
14250 6388.27
14500 6668.74
14750 6952.25
i plotted the numbers in excel, i created a trend line but now i m not able to found a general formula for this serial. Excel give me a function like:
y = 6E-21x6 - 3E-16x5 + 4E-12x4 - 2E-08x3 + 6E-05x2 - 0.0543x + 12.34
but it doesn t works.
If someone can help me about.......

Dc

**Ackbeet** · May 2nd 2011, 07:37 AM

A few questions:

1. Where did this data come from? It looks entirely too smooth to be a real-world signal.
2. What's the independent variable name? Dependent variable name?
3. Do you think the data approaches a slant asymptote as the independent variable goes to positive infinity?
4. What do you want to do with this data? Interpolate? Extrapolate? If the latter, how far out do you want to go?

Cheers.

**dendricala** · May 2nd 2011, 08:14 AM

hello.
first thx a lot for your reply, i really appreciate.
1) the data came from an online game. The are skills point versus cost. I m trying to build a tool that tell to people how much works his avatar. I know is weird, but this is the true.
2) so the x=skill points and the y=value
3) yes, i think so.
4) i would like have a general formula for this serial in order to allow people to know for a x value the relative y. i guess this is named interpolation?

ps: sorry for my bad english...... i m trying hard. thx

**Ackbeet** · May 2nd 2011, 08:22 AM

With regard to 4: it's interpolation if you want to know the y values for x values that are in-between already existing x values. Example: you want to know the y-value corresponding to an x-value of 150. Well, 100 < 150 < 200, and 100 and 200 are both values that you have. That's interpolation. Extrapolation is when you need to go further out, either on the left-hand side or the right-hand side. Example: you want to know the y value corresponding to an x-value of 15000. The closest x-value you have in your data is 14750 < 15000. So 15000 is not bracketed by x values that you already have. Make sense?

So the root question here, in terms of interpolation or extrapolation, is this: what is the maximum x value in which you're interested?

I have a suggestion: if you just need to interpolate, then do the Excel fit you've already done (6th-order polynomial), and then just display more significant figures. You can do that by right-clicking the equation in Excel and choosing Format Trendline Label... Then choose Number -> Scientific, with as many decimal places as you need.

Does this answer your question?

If not, another possibility is to try to fit a hyperbola to your data. If the data asymptotically approaches a straight line (slanted upwards), then it seems to me you might be able to fit a hyperbola to the data pretty accurately. I've never done that before, though, and Excel doesn't have that option. You'd need a more advanced program, or you might re-derive the formula yourself using the standard least-squares fit procedure.

**dendricala** · May 2nd 2011, 09:27 AM

Originally Posted by Ackbeet

With regard to 4: it's interpolation if you want to know the y values for x values that are in-between already existing x values. Example: you want to know the y-value corresponding to an x-value of 150. Well, 100 < 150 < 200, and 100 and 200 are both values that you have. That's interpolation. Extrapolation is when you need to go further out, either on the left-hand side or the right-hand side. Example: you want to know the y value corresponding to an x-value of 15000. The closest x-value you have in your data is 14750 < 15000. So 15000 is not bracketed by x values that you already have. Make sense?

So the root question here, in terms of interpolation or extrapolation, is this: what is the maximum x value in which you're interested?

I have a suggestion: if you just need to interpolate, then do the Excel fit you've already done (6th-order polynomial), and then just display more significant figures. You can do that by right-clicking the equation in Excel and choosing Format Trendline Label... Then choose Number -> Scientific, with as many decimal places as you need.

Does this answer your question?

If not, another possibility is to try to fit a hyperbola to your data. If the data asymptotically approaches a straight line (slanted upwards), then it seems to me you might be able to fit a hyperbola to the data pretty accurately. I've never done that before, though, and Excel doesn't have that option. You'd need a more advanced program, or you might re-derive the formula yourself using the standard least-squares fit procedure.

well i need the extrapolation than, in effect i have the x= 100 and x=200, i need to know about x=120, 146, 176 etcetc. The max value of x in unknown, i would not go more than double of the bigger one. In the txt file attached there are the values that you can use for your commodity to snap in excel. Sadly don t look like any hyperbola fit well.

**Ackbeet** · May 2nd 2011, 10:35 AM

I think you could get the hyperbola to work, at least theoretically, but I'm having to work pretty hard to figure out how to get the general form of the desired hyperbola. I'll let you know if I come up with anything else.

**dendricala** · May 2nd 2011, 11:07 AM

thx a lot. Like i said any help in this matter will be more than welcome.

**chisigma** · May 3rd 2011, 08:09 AM

Originally Posted by dendricala

Hello
first time here, and to be honest i m here because i need help.
I have a data serie :
100 0.12
200 0.25
300 0.42
400 0.68
500 1.08
600 1.5
700 1.83
800 2.12
900 2.51
1000 3.09
1100 3.72
1200 4.2
1300 4.64
1400 5.21
1500 6.07
1600 6.99
1700 7.71
1800 8.35
1900 9.21
2000 10.48
2100 11.85
2200 12.92
2300 13.87
2400 15.13
2500 17.02
2750 21.31
3000 26.69
3250 33.07
3500 41.03
3750 50.51
4000 61.96
4250 75.71
4500 92.18
4750 111.9
5000 135.46
5250 163.58
5500 197.06
5750 236.93
6000 280.42
6250 334.76
6500 396.85
6750 467.43
7000 547.32
7250 637.31
7500 738.28
7750 873.75
8000 1004.9
8250 1142.96
8500 1289.66
8750 1442.97
9000 1604.53
9250 1772.41
9500 1948.16
9750 2129.93
10000 2319.2
10250 2514.19
10500 2716.29
10750 2923.82
11000 3138.08
11250 3357.48
11500 3583.23
11750 3813.81
12000 4050.37
12250 4291.47
12500 4538.16
12750 4789.1
13000 5045.25
13250 5305.34
13500 5570.27
13750 5838.85
14000 6111.88
14250 6388.27
14500 6668.74
14750 6952.25
i plotted the numbers in excel, i created a trend line but now i m not able to found a general formula for this serial. Excel give me a function like:
y = 6E-21x6 - 3E-16x5 + 4E-12x4 - 2E-08x3 + 6E-05x2 - 0.0543x + 12.34
but it doesn t works.
If someone can help me about.......

Dc

A possible approach to the problem is the so called 'last square polynomial approximation' that consists, given data in the form...

..., in the search of a polynomial...

... so that the quantity...

... is minimized. The simplest case is when m=1 [linear polynomial...] and if we define...

... the coefficients are...

For m>1 the problem is a little more diffcult and a different approach must be used...

Kind regards

$\chi$ $\sigma$

**dendricala** · May 3rd 2011, 08:18 AM

Originally Posted by Ackbeet

I think you could get the hyperbola to work, at least theoretically, but I'm having to work pretty hard to figure out how to get the general form of the desired hyperbola. I'll let you know if I come up with anything else.

yup in effect i was considering that x values negative this can be a parabole and can be solved in that way

**Ackbeet** · May 3rd 2011, 08:44 AM

Originally Posted by chisigma

A possible approach to the problem is the so called 'last square polynomial approximation' that consists, given data in the form...

..., in the search of a polynomial...

... so that the quantity...

... is minimized. The simplest case is when m=1 [linear polynomial...] and if we define...

... the coefficients are...

For m>1 the problem is a little more diffcult and a different approach must be used...

Kind regards

$\chi$ $\sigma$

Perhaps; however, the Excel sixth-degree polynomial that has already been tried was not, apparently, accurate enough. I would think that extrapolation would be problematic at best with a polynomial fit, if the data approaches a slant asymptote as the independent variable goes to positive infinity. I'm hoping to figure out how to do a hyperbola fit, with one asymptote being the x-axis, and the other being an arbitrary slant asymptote y = mx + b.

**dendricala** · May 3rd 2011, 09:21 AM

Originally Posted by Ackbeet

Perhaps; however, the Excel sixth-degree polynomial that has already been tried was not, apparently, accurate enough. I would think that extrapolation would be problematic at best with a polynomial fit, if the data approaches a slant asymptote as the independent variable goes to positive infinity. I'm hoping to figure out how to do a hyperbola fit, with one asymptote being the x-axis, and the other being an arbitrary slant asymptote y = mx + b.

yes excel sixth degree was not accurate, but reading around look like excel has actually problem to handle polynomial higher than second degree. In effect also a fourth degree polynomial trend line look nice (but also in this case the generated formula was not accurate). The hyperbola fit with x-axis asymptote look at me the best way.
here the data txt to import for your commodity to generate the hyperbole
thx
dc

**Ackbeet** · May 5th 2011, 02:52 AM

Ok. I've made some progress, though I may need one more equation to get it down to what I want. The general formula for the hyperbola is

$ax^{2}+bxy+cy^{2}+dx+ey+f=0.$

Let us use the notation $y_{+}$ for the upper sheet of the hyperbola (the one we're interested in), and $y_{-}$ for the lower sheet. We want the upper sheet to vanish at negative infinity, and to approach the line

$y=mx+n$

at positive infinity.

Then we wish to impose the following conditions:

$\lim_{x\to-\infty}y_{+}=0,\qquad m=\lim_{x\to\infty}\frac{y_{+}}{x},\qquad n=\lim_{x\to\infty}(y_{+}-mx).$

We may also need the condition that

$\lim_{x\to\infty}y_{-}=0.$

First of all, to save some writing, we note that, while there are six constants to be determined, a, b, c, d, e, f, we can multiply the entire equation by any nonzero constant and not change the equation. Hence, we can normalize one of the constants to 1. I will normalize c = 1, and rewrite:

$ax^{2}+bxy_{+}+y_{+}^{2}+dx+ey_{+}+f=0.$

Next, the negative infinity asymptote. Let us divide the entire equation by $x^{2}$ to obtain

$\lim_{x\to-\infty}\left(a+\frac{by_{+}}{x}+\frac{y_{+}^{2}}{x ^{2}}+\frac{d}{x}+\frac{ey_{+}}{x^{2}}+\frac{f}{x^ {2}}\right)=a=0.$

Hence, we rewrite the equation

$y_{+}^{2}+bxy_{+}+ey_{+}+dx+f=0.$

I have more, but I have to leave for now.

[EDIT]: See below for a correction. Basically, this entire approach is incorrect.

**dendricala** · May 5th 2011, 04:46 AM

Originally Posted by Ackbeet

Ok. I've made some progress, though I may need one more equation to get it down to what I want. The general formula for the hyperbola is

$ax^{2}+bxy+cy^{2}+dx+ey+f=0.$

Let us use the notation $y_{+}$ for the upper sheet of the hyperbola (the one we're interested in), and $y_{-}$ for the lower sheet. We want the upper sheet to vanish at negative infinity, and to approach the line

$y=mx+n$

at positive infinity.

Then we wish to impose the following conditions:

$\lim_{x\to-\infty}y_{+}=0,\qquad m=\lim_{x\to\infty}\frac{y_{+}}{x},\qquad n=\lim_{x\to\infty}(y_{+}-mx).$

We may also need the condition that

$\lim_{x\to\infty}y_{-}=0.$

First of all, to save some writing, we note that, while there are six constants to be determined, a, b, c, d, e, f, we can multiply the entire equation by any nonzero constant and not change the equation. Hence, we can normalize one of the constants to 1. I will normalize c = 1, and rewrite:

$ax^{2}+bxy_{+}+y_{+}^{2}+dx+ey_{+}+f=0.$

Next, the negative infinity asymptote. Let us divide the entire equation by $x^{2}$ to obtain

$\lim_{x\to-\infty}\left(a+\frac{by_{+}}{x}+\frac{y_{+}^{2}}{x ^{2}}+\frac{d}{x}+\frac{ey_{+}}{x^{2}}+\frac{f}{x^ {2}}\right)=a=0.$

Hence, we rewrite the equation

$y_{+}^{2}+bxy_{+}+ey_{+}+dx+f=0.$

I have more, but I have to leave for now.

take your time :-) and thx

**Ackbeet** · May 5th 2011, 07:34 AM

Ok, everything I wrote in Post # 12 is wrong. I'm going to try an entirely different approach: rotations. We start with a simple North-South hyperbola, which we can write as

$y^{2}-q^{2}x^{2}=a>0.$

Here $\pm q$ are the slopes of the asymptotes.

Here is a plot, where $q=1/4.$

What we would like to do is rotate this figure counter-clockwise through some angle such that the $-1/4$ asymptote lines up with the x axis, and the $+1/4$ asymptote becomes the slope of the line corresponding to your data. It turns out that the hyperbola to start with such that when you rotate it through a certain angle, you get one asymptote to be the x axis, and the other the line $y=m(x-p),$ is the following:

$y^{2}-\left(\tan\left(\frac{\tan^{-1}(m)}{2}\right)\right)^{\!\!2}x^{2}=a.$

Next, we rotate the coordinates counter-clockwise through the angle

$\frac{\tan^{-1}(m)}{2}.$

Define

$C=\cos\left(\frac{\tan^{-1}(m)}{2}\right),$ and

$S=\sin\left(\frac{\tan^{-1}(m)}{2}\right).$

Then our new coordinates become

$\begin{bmatrix}x'\\y'\end{bmatrix}=\begin{bmatrix} C &-S\\ S &C\end{bmatrix}\begin{bmatrix}x\\ y\end{bmatrix}.$

Plugging this into our hyperbola equation yields

$(Sx+Cy)^{2}-\left(\tan\left(\frac{\tan^{-1}(m)}{2}\right)\right)^{\!\!2}(Cx-Sy)^{2}=a.$

Finally, to translate the hyperbola to the right by $p$ units, we simply replace $x$ by $x-p$ to obtain

$(S(x-p)+Cy)^{2}-\left(\tan\left(\frac{\tan^{-1}(m)}{2}\right)\right)^{\!\!2}(C(x-p)-Sy)^{2}=a.$

Solving for y yields the upper sheet equation of

$y_{+}=\frac{\sqrt{4(4C^{2}-m^{2}S^{2})(4a+(C^{2}m^{2}-4S^{2})(x-p)^{2})+4C^{2}(4+m^{2})^{2}S^{2}(x-p)^{2}}+2C(4+m^{2})S(x-p)}{8C^{2}-2m^{2}S^{2}}.$

So this is the equation to which you're going to fit your data. It has three parameters: the slope $m,$ the x-intercept $p,$ and an arbitrary parameter $a$ that measures how close the hyperbola gets to the intersection of its two asymptotes.

I have plotted this equation, and it looks right to me.

That's all for now. Next step: least-squares fit!

**dendricala** · May 5th 2011, 08:25 AM

Originally Posted by Ackbeet

Ok, everything I wrote in Post # 12 is wrong. I'm going to try an entirely different approach: rotations. We start with a simple North-South hyperbola, which we can write as

$y^{2}-q^{2}x^{2}=a>0.$

Here $\pm q$ are the slopes of the asymptotes.

Here is a plot, where $q=1/4.$

What we would like to do is rotate this figure counter-clockwise through some angle such that the $-1/4$ asymptote lines up with the x axis, and the $+1/4$ asymptote becomes the slope of the line corresponding to your data. It turns out that the hyperbola to start with such that when you rotate it through a certain angle, you get one asymptote to be the x axis, and the other the line $y=m(x-p),$ is the following:

$y^{2}-\left(\tan\left(\frac{\tan^{-1}(m)}{2}\right)\right)^{\!\!2}x^{2}=a.$

Next, we rotate the coordinates counter-clockwise through the angle

$\frac{\tan^{-1}(m)}{2}.$

Define

$C=\cos\left(\frac{\tan^{-1}(m)}{2}\right),$ and

$S=\sin\left(\frac{\tan^{-1}(m)}{2}\right).$

Then our new coordinates become

$\begin{bmatrix}x'\\y'\end{bmatrix}=\begin{bmatrix} C &-S\\ S &C\end{bmatrix}\begin{bmatrix}x\\ y\end{bmatrix}.$

Plugging this into our hyperbola equation yields

$(Sx+Cy)^{2}-\left(\tan\left(\frac{\tan^{-1}(m)}{2}\right)\right)^{\!\!2}(Cx-Sy)^{2}=a.$

Finally, to translate the hyperbola to the right by $p$ units, we simply replace $x$ by $x-p$ to obtain

$(S(x-p)+Cy)^{2}-\left(\tan\left(\frac{\tan^{-1}(m)}{2}\right)\right)^{\!\!2}(C(x-p)-Sy)^{2}=a.$

Solving for y yields the upper sheet equation of

$y_{+}=\frac{\sqrt{4(4C^{2}-m^{2}S^{2})(4a+(C^{2}m^{2}-4S^{2})(x-p)^{2})+4C^{2}(4+m^{2})^{2}S^{2}(x-p)^{2}}+2C(4+m^{2})S(x-p)}{8C^{2}-2m^{2}S^{2}}.$

So this is the equation to which you're going to fit your data. It has three parameters: the slope $m,$ the x-intercept $p,$ and an arbitrary parameter $a$ that measures how close the hyperbola gets to the intersection of its two asymptotes.

I have plotted this equation, and it looks right to me.

That's all for now. Next step: least-squares fit!

i m so impressed. I hope you have a good job, because you deserve it!

Thread: Find a formula for serial data

LinkBack

Thread Tools

Display

Find a formula for serial data

Some Progress

Similar Math Help Forum Discussions

Help needed working out formula from data table

Annuity and serial loan with a twist

Working out a formula from a set of data

Formula needed, all data available

Finding a formula for this data

Search Tags