polynomial function
cerr
I'm trying to make sense out of a polynomial function from excel,
it gives me following:
y = 2E-18x^6 - 2E-14x^5 + 1E-10x^4 - 2E-07x^3 + 0.0002x^2 - 0.0373x +
70.098
and i want to translate this into (normal math) where x is numbers
from 0 - 4069.
following doesnt' seem to be right:
2/10^18*x^6 - 2/10^14*x^5+1/10^10*x&3+0.0002*x^2-0.0373*x+70.098
But I don't see my mistake - can someone help me please?
Thank you!
Rick Decker
2E-07 is generally understood to mean 2 * 10^{-7}, in other
words, 0.0000002. Try that.
Regards,
Rick
Ray Vickson
There IS no mistake; 2/10^18 (or 2*10^(-18)) is exactly what is meant
by 2E-18. Why do you think you are wrong? Of course, you may have a
problem with roundoff errors in computations, especially since you
have alternating signs, but that is another issue entirely.
R.G. Vickson
> Thank you!
James Waldby
Something's wrong with the sequence 1/10^10*x&3+0.0002*x^2;
apparently the x^4 and x^3 terms are messed up.
To OP -- use more whitespace, eg a space on each side of
each + or -, and instead of writing terms like 2/10^14*x^5
write them like 2*x^5/10^14 or perhaps (2*x^5)/10^14
--
jiw
Mohan Pawar
> Hi There,
>
> I'm trying to make sense out of a polynomial function from excel,
> it gives me following:
> y = 2E-18x^6 - 2E-14x^5 + 1E-10x^4 - 2E-07x^3 + 0.0002x^2 - 0.0373x +
> 70.098
> and i want to translate this into (normal math) where x is numbers
> from 0 - 4069.
> following doesnt' seem to be right:
because expression below has maybe cut and paste errors. x^4 term is
missing. Also, x^3 term has a typo since it is written as x&3. Correct
these two errors and both expressions will be same for the Excel
purpose.
> 2/10^18*x^6 - 2/10^14*x^5+1/10^10*x&3+0.0002*x^2-0.0373*x+70.098
> But I don't see my mistake - can someone help me please?
> Thank you!
Best regards.
Mohan Pawar
Website: http://www.mpClasses.com
YouTube: http://www.youtube.com/user/MathsPhysicsClasses
---------------------------------------------------------------------------
Online test preparation in Maths/Physics: ACT/SAT/AP and IIT JEE
---------------------------------------------------------------------------
US Central Time:5:36 PM 3/28/2011
cerr
Okay, I tried it again and came up with this:
=2/(18*C7^6) - 2/(14*C7^5) + 1/(10*C7^4) - 2/(7*C7^3) + 0.0002*C7^2 -
0.0375*C7 + 70.135
where Excel; calculates for me following:
y = 2E-18x6 - 2E-14x5 + 1E-10x4 - 2E-07x3 + 0.0002x2 - 0.0375x +
70.135
My data look as follows:
1 70
600 80
1200 90
1800 100
2400 100
3000 100
3600 50
4096 0
and the formula excel came up with draws me a nice line following the
points in an aproximate matter but if I leave the points through my
function above I get following values:
69.88023968
119.635
313.135
650.635
1132.135
1757.635
2527.135
3271.9782
Which are totally wrong.... any hints?
Thanks!
Ron
James Waldby
>On Mar 28, 2:25 pm, James Waldby <n...@valid.invalid> wrote:
>> > On Mar 28, 1:30 pm, cerr <ron.egg...@gmail.com> wrote:
>> >> I'm trying to make sense out of a polynomial function from excel, it
>> >> gives me following:
>> >> y = 2E-18x^6 - 2E-14x^5 + 1E-10x^4 - 2E-07x^3 + 0.0002x^2 - 0.0373x +
>> >> 70.098
>> >> and i want to translate this into (normal math) where x is numbers from
>> >> 0 - 4069.
>> >> following doesnt' seem to be right:
>> >> 2/10^18*x^6 - 2/10^14*x^5+1/10^10*x&3+0.0002*x^2-0.0373*x+70.098 But I
>> >> don't see my mistake - can someone help me please?
>> Something's wrong with the sequence 1/10^10*x&3+0.0002*x^2;
>> apparently the x^4 and x^3 terms are messed up.
>>
>> To OP -- use more whitespace, eg a space on each side of
>> each + or -, and instead of writing terms like 2/10^14*x^5
>> write them like 2*x^5/10^14 or perhaps (2*x^5)/10^14
>
> Okay, I tried it again and came up with this:
> =2/(18*C7^6) - 2/(14*C7^5) + 1/(10*C7^4) - 2/(7*C7^3) + 0.0002*C7^2 -
> 0.0375*C7 + 70.135
> where Excel; calculates for me following:
> y = 2E-18x6 - 2E-14x5 + 1E-10x4 - 2E-07x3 + 0.0002x2 - 0.0375x + 70.135
Presumably superscript indicators are missing and that stands for
y = 2E-18*x^6 - 2E-14*x^5 + 1E-10*x^4 - 2E-07*x^3 + 0.0002*x^2 - 0.0375*x
+ 70.135
and presumably C7 represents independent variable x in your formula.
The notation 2E-18 stands for 2 * 10^(-18), which is equal to 2/(10^18).
With C7 in place of x, term 2E-18*x^6 is (2*C7^6)/(10^18)
Do you have a reason not to use nE-x notation? The clearest way to
write the formula for Excel probably is as follows:
(C7^6)*2E-18 - (C7^5)*2E-14 + (C7^4)*1E-10 - (C7^3)*2E-07
+ (C7^2)*0.0002 - C7*0.0375 + 70.135
>My data look as follows:
>
>1 70
>600 80
>1200 90
>1800 100
>2400 100
>3000 100
>3600 50
>4096 0
>
>and the formula excel came up with draws me a nice line following the
>points in an aproximate matter but if I leave the points through my
>function above I get following values:
>69.88023968
>119.635
>313.135
>650.635
>1132.135
>1757.635
>2527.135
>3271.9782
>
>Which are totally wrong.... any hints?
--
jiw
Ray Vickson
When I plug in your y formula into Maple and evaluate it to 20 digit
accuracy (then print out lower-precision results) I get:
x y
1 70.0977
600 87.9331
1200 131.1006
1800 224.1058
2400 474.7762
3000 1055.6350
3600 2252.4245
4096 4061.8834
I get essentially the same results whether I let Maple use the 10-
digit default or go up to 50 or 100 digits or more.
So, yes, EXCEL is giving you incorrect results. As to why: I cannot
say. It *should* give results at least as accurate as Maple's lower-
precision evaluations (10 to 15 digits).
Are you sure you have entered the formula correctly?
You do realize, I hope, that your formula for y does not resemble the
actual data.
When I use Maple to get a least-squares fit of a 6th degree polynomial
to the 8 data points, I get the formiula
y:= .925805270130065646e-20*x + .156234442801747108e-16*x^2 + .
230396972218602821e-13*x^3 + .235027383252367024e-10*x^4 - .
118380786737938880e-13*x^5 + .149030438985901372e-17*x^6
(accompanied by a warning that the model is not of full rank). Note
that this fit has no constant term, just terns in x, x^2,... ,x^6. The
results are:
x y y-fit
1 70.0000 0.0000
600 80.0000 2.1950
1200 90.0000 23.7284
1800 100.0000 73.7225
2400 100.0000 121.9447
3000 100.0000 113.5012
3600 50.0000 33.5934
4096 0.0000 4.8223
This not nearly as bad as the results from your function, although I
would still doubt its usefulness.
Let me ask: what are you trying to do? Are you sure you are using the
best methodology? Using simple splines will give you much nicer
results
R.G. Vickson
Ray Vickson
The problem is one of "scaling". Here is what I get using Maple 11,
fitting a 6th degree polynomial to your data via least squares, once
with the original data and once using t = x/100 instead of x:
x y y-fit(x) y-fit(t)
1 70.0000 0.0000 70.0981
600 80.0000 2.1950 79.3121
1200 90.0000 23.7284 92.0744
1800 100.0000 73.7225 96.5124
2400 100.0000 121.9447 103.5406
3000 100.0000 113.5012 97.8086
3600 50.0000 33.5934 50.8070
4096 0.0000 4.8223 -0.1532
The y-fit is as in my previous posting:
y-fit(x) = 0.925805270130065646e-20*x + 0.156234442801747108e-16*x^2
+ 0.230396972218602821e-13*x^3 + 0.235027383252367024e-10*x^4
- 0.118380786737938880e-13*x^5 + 0.149030438985901372e-17*x^6
Using instead t = x/100 (so, y to fitting to t = .01,6,12,18,...) we
have
y-fit(t) = 70.1354058754993446-3.74600678952053868*t
+1.73143889291111153*t^2-.191874952796174225*t^3+.
955582224878641016e-2*t^4-.219241349194729052e-3*t^5+.
185148272460380998e-5*t^6.
Note that if we put back t = x/100 this gives a new formula in x:
newy-fit(x) = 70.1354058754993446-.3746006790e-1*x+.1731438893e-3*x^2-.
1918749528e-6*x^3+.9555822249e-10*x^4-.2192413492e-13*x^5+.
1851482725e-17*x^6.
Unlike the original fit, this one has a constant term (not involving
x), and altogether does a much better job.
The fitting procedure in the scaled problem, using t instead of x,
does not give any warnings about not having full rank. I suspect the
problem with the original fit could be avoided by doing the least-
squares fit manually---setting up and solving the resulting linear
equations in higher precision (say using 20-40 digits in the
computations)---rather than using the canned "Statistics" package.
However, I have not tried this. Of course, such high-precision
methods are not available to you when you use EXCEL. However,
*scaling* the problem is certainly an option, and I *recommend it
highly*. Try it again, using t = .01, 6, 12, 18,... instead of x = 1,
600,1200, ... .
R.G. Vickson