There is a function in excel called 'linest' that will perform a multiple linear regression with a single y column of data and multiple x columns. Is there an equivilant in Lavview?

[sjh]

I am still not getting sensible results from this function when compared to excel.

I have uploaded the vi and data for perusal!

Any ideas why this is not providing coefficents that align with excel

[DSPGuy]

The problem is with your H matrix.  The condition number is extremely large (~1.7E+23), indicating that whatever the solver returns is probably garbage.  To contrast, the Excel example I coded earlier has a condition number of ~1E+5.  You could probably try several different solvers from different SW packages and each would yield a different answer, and each would be suspect and not reliable.

-Jim

[sjh]

Thanks for your help 

Ok - That example was a bit extreme.  The attached data on an excel spreadsheet and Vi is from a real set of data.

The excel returns seeming sensible results - labview appears not too.

I assume that I am running beyond what LV can do (or the version I have).

[altenbach]

Columns 3, 4, 5 are multiples of columns 0, 1, 2 (factor of 500), thus your data is only good for four parameters, not seven.

Simply delete the first three columns and the problem becomes mathematically solvable with any of the algorithms.

(condition number is now 17(!!) orders of magnitude better!)

If excel gives you a seemingly valid result for seven columns, you cannot trust it for anything. The excel result is completely meaningless.

[sjh]

I understand the point made, but unfortunately the calculation is specified (with those multiples)  in a number  British and European standards.

What I can trying to do is use labview in an automated package to do this instead of excel.

The excel results are accepted as valid.   

SJH

[altenbach]

---

sjh wrote:

The excel results are accepted as valid.

---

I don't think you understand the point at all! 😮

Who "accepts" this???? That makes absolutely no mathematical sense!!! Whoever insists on that better brush up on some basic mathematics.

If you have linearly dependent columns, there is an infinite number of equally good solutions and no reasonable way of picking the right one. (e.g. looking at the first and fourth parameter [A=0; D=Z/500], [A=Z; D=0], [A=100000; D=(Z-A)/500], etc. would all be equivalent solutions. Only Z=A+D*500=const. is an independent parameter. Same for B vs. E and C vs. F).

What you need to do is solve the 4 parameter problem, then derive the solutions for other units by appropriate scaling. The seven parameters are not independent so you cannot simply throw them all into one pot and expect 7 independent numbers. This is total nonsense!

[DSPGuy · Accepted solution]

Christian has a good point, particularly if you consider the condition number.  However, there is a method that helps to deal with very ill conditioned matrices, namely regularization.  Tikhonov and truncated SVD are two very common approaches.  The idea is to add additional constraints to the system such that the results will be better conditioned.  For example, solving an overdetermined system is equivalent to saying:

minimize ||Ax-b|| 

Assume all norms are 2-norm.

This problem can yield very large values of X if the system is ill-conditioned.  Suppose we restate the problem in this way:

minimize ||x|| s.t. Ax=b

The idea is that sacrificing a slight amount of accuracy can yield a great reduction in the magnitude of the x values.  

Excel does just this (not sure of their exact algorithm), and LabVIEW also does this in the 'Solve Linear Equations.vi' but not in the 'General LS Linear Fit.vi'.  Attached is a modified version of your VI that shows both methods.  

Now the problem is to compare the results from LabVIEW to Excel.  As Christian points out your system is rank deficient, meaning that there is not a unique solution.  That being said, the LabVIEW result, using  'Solve Linear Equations.vi' is very close to the Excel results.

Below are the raw results returned from Excel, LabVIEW  'General LS Linear Fit.vi', and LabVIEW 'Solve Linear Equations.vi'

 LabVIEW  'General LS Linear Fit.vi'

                            -8.209501E+16    -5.851628E+15    1.928267E+18    1.641900E+14    1.170326E+13    -3.856533E+15    -6.422233E+2

LabVIEW 'Solve Linear Equations.vi'

                            1.115257E-4         7.550531E-5        9.530940E-4       5.576283E-2       0.03775265         0.4765470           4.689803

Excel

27.88152296        0                           0                         0                        0                        0.03775281         0.476548             4.689804

Excel returns an extra parameter.  This is the first value, 27.88, and is the constant term of the expression.   LabVIEW does not automatically add this to the model and requires that there be a column of 1's in the matrix.  In this problem you have that column already, so it is possible to compare.  The column of 1's, the column of 500's and the constant term are all equivalent, meaning two of those terms may be combined into the third.  Take the coefficient corresponding to the 500's column and multiply by 500, then add to the 1's column coefficient (500*5.576283E-2 + 1.115257E-4 = 27.88152652).  This is equal to the Excel constant term to several decimal places.  The rest of the columns are quite close to the Excel solution, and it is possible to combine the other linearly dependent columns to achieve even better agreement. 

-Jim

[DSPGuy]

Christians last post really highlights the problem here.  The regularized LabVIEW solution is still not unique.  It is possible to identify the columns of constant terms and combine them into a single lumped constant term.  Detecting the other rank deficiencies is certainly harder to automate, and is better done before fitting if at all possible.

-Jim

[sjh]

Thanks for the input.

There are only three true variables in that equation - with the 'y' being a 'time to'. The rest are multiples/divisions of each other or recipricals.

The 'rules of engagement' including the number of variables and how these arrived at is beyond my control. The mathmatical method of producing a constant + 7 variables is also defined. The Standard (& yes it is a BS/EN says) ' the multiple linear regression analysis is performed using the equation'. 

I do understand the 'mathmatical' points you are making - and from the comments you have made, I now have an even greater understanding.

The point of the question orignally posted and followed up was to improve my understanding of Labview. excel and this method of calculation. - and to get  Labview system working. The 'acid test' for the programme is when the results are compared back against an.......... excel spreadsheet.

The way that calculation is used and whether it is appropriate, well that's for another day.

Thanks again for your help.

[altenbach]

---

sjh wrote:

The Standard (& yes it is a BS/EN says)

---

Ouch! That's just silly!

Simply write a subVI that deletes the first three rows, solves the well conditions problem, prepend three zeroes and output an array of size=7. 😄

(password the diagram and don't tell anyone... ;))

Message Edited by altenbach on 09-03-2009 12:14 PM