That's really fascinating! I just saw another example yesterday. If
the argument intercept is left blank, which is supposed to default to
true, if you have, say a ±10 volt output, then the LINEST function
returns a zero coefficient for x, meaning all you have is a constant
and, no best fit line. You have to set the argument to false, and then
it returns a non-zero coefficient for x. I just realized that this
doesn't happen if the zero intercept occurs at one end. We calibrate
unipolar, like 0 to 10 Volts, with that sequence of numbers (and yes, we
do get down to zero ± a couple of millivolts), the LINEST does return an
x coefficient. Then it only has a problem with a zero intercept in the
middle of the range. Now I only know this happens in Excel 2003, I
don't know about more recent versions.
I have Excel 2007 at home, I should try it here.
On 11/15/2011 4:25 PM, Eli Allen wrote:
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
