using linest with very small numbers
 

When I use very small numbers with LINEST it returns zeros. Please help me
solve this problem

using linest with very small numbers
 

flow guy wrote...
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.

using linest with very small numbers
 

"Harlan Grove" wrote:

flow guy wrote...
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.

Thanks Harlan,

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.

using linest with very small numbers
 

flow guy wrote...
....
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.

using linest with very small numbers
 

"Harlan Grove" wrote:

flow guy wrote...
....
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.



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.