 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / August 2006using linest with very small numbers
When I use very small numbers with LINEST it returns zeros. Please help me solve this problem 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. > flow guy wrote... > >When I use very small numbers with LINEST it returns zeros. Please help me [quoted text clipped - 4 lines] > > 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. 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. > flow guy wrote... > .... [quoted text clipped - 24 lines] > 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.