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
