This makes it seem like 2003 fixed that function: http://support.microsoft.com/kb/828533
On Mon, Nov 14, 2011 at 7:26 PM, Steve Tomporowski <[email protected]> wrote: > Where I work, we calculate non-linearity by using a "least-squares" line or > a first order (linear) approximation. For this you can use the linest > function, which is good for up to a 6th order polynomial. In Excel 2003, > Microsoft broke the linest function such that it fails when the function > passes through zero or has both positive and negative values. There is a > hack to make it work, but now the feeling is that it's unreliable. > Microsoft recommends getting statistical parameters a different way than > linest, but that method can't be used to generate a least-squares line. Now > there is no reliable function in Excel to generate a best-fit straight line > (least squares). In response, I had to rework all of our calibration sheets > by generating the least squares via the matrix formula, which was a little > bit involved and took a bit of time. Of course the math is horrible once > you get to 2nd order equations an above, luckily nobody (well, nobody but > me, here) uses anything of higher order. > > Hopefully above makes sense. There is a bit of explanation of the linest > function in Excel help. > > Steve > > On 11/14/2011 7:06 PM, DSinc wrote: >> >> Steve, >> Can you explain the last line of your share: "Also, Excel 2003 broke the >> linest function and even Microsoft says do not use it." >> I admit that I continue Office2003 with some tweaks. O2K3 has been good to >> me. I can move forward. >> >> I am beginning to think it is time to spend coins and update my MSOffice >> version. >> Well, as long at whatever it is plays happy with WinXPpro. ATM, I choose >> NOT to be driven to W7 >> just for another tweak of Office. >> >> I figure I can now go to my local used bookstore and find an Office2007 CD >> in readable shape.(?) >> I already have 'patches' from MS that indicate I have parts of Office2007 >> residing here now. Fine. >> Yes, I am still testing OpenOffice also; and, may still just go there. >> >> It is tough to toss ~30yrs of experience and familiarity with MSOffice >> away. I am really slow with big, major MS apps.......... :) >> Yes! Remaining a bit stone-age! LOL! >> Duncan >> >> >> On 11/14/2011 18:15, Steve Tomporowski wrote: >>> >>> Whenever Excel detects a file made under a previous version of Excel >>> (even if it's just a service pack) it will ask you if you want to save the >>> 'changes', even if you haven't made any. It says, it 'recalculates' the >>> spreadsheet. This drives us crazy at work because we have something like 10 >>> years of calibration sheets in various versions of Excel. Of course, if you >>> do 'upgrade' you loose the file date, it changes it to today, then we can't >>> tell whether it's really been modified recently or not. Also, Excel 2003 >>> broke the linest function and even Microsoft says do not use it. >>> >>> Steve >>> >>> On 11/14/2011 6:00 PM, DSinc wrote: >>>> >>>> Is there a good reason why Win Excel wishes me to 'upgrade' my old >>>> spreadsheets on either Save or Close post the install of SP3 for Office? >>>> Not quite sure how to react to this. >>>> I use Office 2003, but with some new Office 2007 stuff now too....... :) >>>> Wondering only............. :) >>>> Duncan >>>> >>> >>> > >
