G
Guest
When I use very small numbers with LINEST it returns zeros. Please help me
solve this problem
solve this problem
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
H
Harlan Grove
flow guy wrote...
Rescale. If your data is on the order of 1E-9, multiply it by 1E9, pass
the result through LINEST, then divide the results by 1E9.
If that's not practical, provide some sample data.
When I use very small numbers with LINEST it returns zeros. Please help me
solve this problem
Rescale. If your data is on the order of 1E-9, multiply it by 1E9, pass
the result through LINEST, then divide the results by 1E9.
If that's not practical, provide some sample data.
G
Guest
Thanks Harlan,Harlan Grove said:flow guy wrote...
Rescale. If your data is on the order of 1E-9, multiply it by 1E9, pass
the result through LINEST, then divide the results by 1E9.
If that's not practical, provide some sample data.
The data I use looks something like this
K Factor C Factor Test^2 Test^3 Test^4 Test^5 Test^6
0.06992564 2.15585E-05 4.64767E-10 1.00197E-14 2.16008E-19 4.65681E-24 1.00394E-28
0.069938392 2.01887E-05 4.07582E-10 8.22853E-15 1.66123E-19 3.3538E-24 6.77088E-29
0.069927604 1.91409E-05 3.66372E-10 7.01268E-15 1.34229E-19 2.56925E-24 4.91776E-29
0.069891316 1.78307E-05 3.17935E-10 5.66902E-15 1.01083E-19 1.80238E-24 3.21378E-29
0.069904062 1.66161E-05 2.76095E-10 4.58761E-15 7.62282E-20 1.26662E-24 2.10462E-29
0.069906181 1.56499E-05 2.4492E-10 3.83297E-15 5.99856E-20 9.38769E-25 1.46916E-29
0.069855866 1.45987E-05 2.13121E-10 3.11129E-15 4.54206E-20 6.63081E-25 9.6801E-30
0.069882382 1.34891E-05 1.81956E-10 2.45443E-15 3.3108E-20 4.46598E-25 6.02421E-30
0.069861887 1.23707E-05 1.53033E-10 1.89313E-15 2.34192E-20 2.89712E-25 3.58393E-30
0.0698582 1.14754E-05 1.31686E-10 1.51115E-15 1.73411E-20 1.98997E-25 2.28357E-30
0.069802252 1.04333E-05 1.08854E-10 1.1357E-15 1.18491E-20 1.23625E-25 1.28982E-30
0.069753771 9.37329E-06 8.78586E-11 8.23524E-16 7.71913E-21 7.23536E-26 6.78191E-31
0.069680808 8.33545E-06 6.94797E-11 5.79144E-16 4.82743E-21 4.02388E-26 3.35408E-31
0.069663806 7.20167E-06 5.1864E-11 3.73508E-16 2.68988E-21 1.93716E-26 1.39508E-31
0.069701526 6.25081E-06 3.90726E-11 2.44235E-16 1.52667E-21 9.5429E-27 5.96508E-32
0.069556041 5.2374E-06 2.74303E-11 1.43664E-16 7.52424E-22 3.94074E-27 2.06392E-32
0.069440932 4.18904E-06 1.75481E-11 7.35098E-17 3.07936E-22 1.28996E-27 5.40369E-33
0.069532739 3.12485E-06 9.7647E-12 3.05133E-17 9.53494E-23 2.97953E-28 9.31059E-34
0.069871533 2.10888E-06 4.44736E-12 9.37895E-18 1.9779E-23 4.17116E-29 8.79646E-35
0.06988299 1.10579E-06 1.22278E-12 1.35215E-18 1.4952E-24 1.65338E-30 1.8283E-36
It is X and Y values and X values raised to different powers. This data is
anylized in to produce coefficeints for curve fits up to the 6th order. This
spread sheet worked well in office 2000, but is producing zeros for
coefficienets for the larger order fits.
G
Guest
Thanks Harlan,Harlan Grove said:flow guy wrote...
Rescale. If your data is on the order of 1E-9, multiply it by 1E9, pass
the result through LINEST, then divide the results by 1E9.
If that's not practical, provide some sample data.
The data I use looks something like this
K Factor C Factor Test^2 Test^3 Test^4 Test^5 Test^6
0.06992564 2.15585E-05 4.64767E-10 1.00197E-14 2.16008E-19 4.65681E-24 1.00394E-28
0.069938392 2.01887E-05 4.07582E-10 8.22853E-15 1.66123E-19 3.3538E-24 6.77088E-29
0.069927604 1.91409E-05 3.66372E-10 7.01268E-15 1.34229E-19 2.56925E-24 4.91776E-29
0.069891316 1.78307E-05 3.17935E-10 5.66902E-15 1.01083E-19 1.80238E-24 3.21378E-29
0.069904062 1.66161E-05 2.76095E-10 4.58761E-15 7.62282E-20 1.26662E-24 2.10462E-29
0.069906181 1.56499E-05 2.4492E-10 3.83297E-15 5.99856E-20 9.38769E-25 1.46916E-29
0.069855866 1.45987E-05 2.13121E-10 3.11129E-15 4.54206E-20 6.63081E-25 9.6801E-30
0.069882382 1.34891E-05 1.81956E-10 2.45443E-15 3.3108E-20 4.46598E-25 6.02421E-30
0.069861887 1.23707E-05 1.53033E-10 1.89313E-15 2.34192E-20 2.89712E-25 3.58393E-30
0.0698582 1.14754E-05 1.31686E-10 1.51115E-15 1.73411E-20 1.98997E-25 2.28357E-30
0.069802252 1.04333E-05 1.08854E-10 1.1357E-15 1.18491E-20 1.23625E-25 1.28982E-30
0.069753771 9.37329E-06 8.78586E-11 8.23524E-16 7.71913E-21 7.23536E-26 6.78191E-31
0.069680808 8.33545E-06 6.94797E-11 5.79144E-16 4.82743E-21 4.02388E-26 3.35408E-31
0.069663806 7.20167E-06 5.1864E-11 3.73508E-16 2.68988E-21 1.93716E-26 1.39508E-31
0.069701526 6.25081E-06 3.90726E-11 2.44235E-16 1.52667E-21 9.5429E-27 5.96508E-32
0.069556041 5.2374E-06 2.74303E-11 1.43664E-16 7.52424E-22 3.94074E-27 2.06392E-32
0.069440932 4.18904E-06 1.75481E-11 7.35098E-17 3.07936E-22 1.28996E-27 5.40369E-33
0.069532739 3.12485E-06 9.7647E-12 3.05133E-17 9.53494E-23 2.97953E-28 9.31059E-34
0.069871533 2.10888E-06 4.44736E-12 9.37895E-18 1.9779E-23 4.17116E-29 8.79646E-35
0.06988299 1.10579E-06 1.22278E-12 1.35215E-18 1.4952E-24 1.65338E-30 1.8283E-36
It is X and Y values and X values raised to different powers. This data is
anylized in to produce coefficeints for curve fits up to the 6th order. This
spread sheet worked well in office 2000, but is producing zeros for
coefficienets for the larger order fits.
H
Harlan Grove
flow guy wrote...
....
As I said, rescale. I multiplied your C Factor values by 1E5 (100,000),
your Test^2 (C Factor squared) by 1E10, your Test^3 (C Factor cubed) by
1E15, etc. Then I called LINEST on your K Factor values as Y and the
rescaled C Factor and Test^# values as X getting
LINEST(Y,X,1,1):
-0.0001934 0.0008567 -0.0002688 -0.0039330 0.0071510 -0.0041250
0.0703100
0.0005494 0.0037450 0.0098640 0.0126100 0.0080140 0.0023030
0.0002191
0.8634398 6.595E-05 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
13.6945294 13______ #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
3.5740E-07 5.655E-08 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
The top line are the coefficient estimators and the constant term.
Multiply the first six, the coefficient terms in reverse order, by
1E30, 1E25, 1E20, 1E15, 1E10 and 1E5, respectively. The restulting
values are the coefficients applicable to the original C Factor and
Test^# values.
....
The data I use looks something like this ....
It is X and Y values and X values raised to different powers. This data is
anylized in to produce coefficeints for curve fits up to the 6th order. This
spread sheet worked well in office 2000, but is producing zeros for
coefficienets for the larger order fits.
As I said, rescale. I multiplied your C Factor values by 1E5 (100,000),
your Test^2 (C Factor squared) by 1E10, your Test^3 (C Factor cubed) by
1E15, etc. Then I called LINEST on your K Factor values as Y and the
rescaled C Factor and Test^# values as X getting
LINEST(Y,X,1,1):
-0.0001934 0.0008567 -0.0002688 -0.0039330 0.0071510 -0.0041250
0.0703100
0.0005494 0.0037450 0.0098640 0.0126100 0.0080140 0.0023030
0.0002191
0.8634398 6.595E-05 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
13.6945294 13______ #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
3.5740E-07 5.655E-08 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
The top line are the coefficient estimators and the constant term.
Multiply the first six, the coefficient terms in reverse order, by
1E30, 1E25, 1E20, 1E15, 1E10 and 1E5, respectively. The restulting
values are the coefficients applicable to the original C Factor and
Test^# values.
H
Harlan Grove
flow guy wrote...
....
As I said, rescale. I multiplied your C Factor values by 1E5 (100,000),
your Test^2 (C Factor squared) by 1E10, your Test^3 (C Factor cubed) by
1E15, etc. Then I called LINEST on your K Factor values as Y and the
rescaled C Factor and Test^# values as X getting
LINEST(Y,X,1,1):
-0.0001934 0.0008567 -0.0002688 -0.0039330 0.0071510 -0.0041250
0.0703100
0.0005494 0.0037450 0.0098640 0.0126100 0.0080140 0.0023030
0.0002191
0.8634398 6.595E-05 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
13.6945294 13______ #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
3.5740E-07 5.655E-08 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
The top line are the coefficient estimators and the constant term.
Multiply the first six, the coefficient terms in reverse order, by
1E30, 1E25, 1E20, 1E15, 1E10 and 1E5, respectively. The restulting
values are the coefficients applicable to the original C Factor and
Test^# values.
....
The data I use looks something like this ....
It is X and Y values and X values raised to different powers. This data is
anylized in to produce coefficeints for curve fits up to the 6th order. This
spread sheet worked well in office 2000, but is producing zeros for
coefficienets for the larger order fits.
As I said, rescale. I multiplied your C Factor values by 1E5 (100,000),
your Test^2 (C Factor squared) by 1E10, your Test^3 (C Factor cubed) by
1E15, etc. Then I called LINEST on your K Factor values as Y and the
rescaled C Factor and Test^# values as X getting
LINEST(Y,X,1,1):
-0.0001934 0.0008567 -0.0002688 -0.0039330 0.0071510 -0.0041250
0.0703100
0.0005494 0.0037450 0.0098640 0.0126100 0.0080140 0.0023030
0.0002191
0.8634398 6.595E-05 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
13.6945294 13______ #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
3.5740E-07 5.655E-08 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
The top line are the coefficient estimators and the constant term.
Multiply the first six, the coefficient terms in reverse order, by
1E30, 1E25, 1E20, 1E15, 1E10 and 1E5, respectively. The restulting
values are the coefficients applicable to the original C Factor and
Test^# values.
G
Guest
Harlan Grove said:flow guy wrote...
....
As I said, rescale. I multiplied your C Factor values by 1E5 (100,000),
your Test^2 (C Factor squared) by 1E10, your Test^3 (C Factor cubed) by
1E15, etc. Then I called LINEST on your K Factor values as Y and the
rescaled C Factor and Test^# values as X getting
LINEST(Y,X,1,1):
-0.0001934 0.0008567 -0.0002688 -0.0039330 0.0071510 -0.0041250
0.0703100
0.0005494 0.0037450 0.0098640 0.0126100 0.0080140 0.0023030
0.0002191
0.8634398 6.595E-05 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
13.6945294 13______ #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
3.5740E-07 5.655E-08 #N/A_____ #N/A____ #N/A_____ #N/A____ #N/A
The top line are the coefficient estimators and the constant term.
Multiply the first six, the coefficient terms in reverse order, by
1E30, 1E25, 1E20, 1E15, 1E10 and 1E5, respectively. The restulting
values are the coefficients applicable to the original C Factor and
Test^# values.
Thanks Harlan, but changing our spreadsheets is not realistic. We have been
using this same routine since office '97. The real problem seems to be with
our new version of office '03. We are only using sp2. Someone in our group
found the answer at http://support.microsoft.com/default.aspx/kb/887964/ It
is easier for us to switch back to 2000 or do the regestry hack than it is to
download sp3 (corporate stuff). It is just very disconcerting to open an old
workbook and find it giving different answers than it did a day ago.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.