Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using linest with very small numbers
When I use very small numbers with LINEST it returns zeros. Please help me
solve this problem |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I show break in bar graph to show large and small numbers | Charts and Charting in Excel | |||
How do I show break in bar graph to show large and small numbers | Charts and Charting in Excel | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
Small font on row & column numbers - wierd | Excel Discussion (Misc queries) |