Fun with Excel

Setting Up The Excel Sheet For Calculating P Values

noreply@blogger.com (ddadmin) — Sat, 08 Jan 2011 06:56:00 +0000

Enter data into the cells. The following are some recommended layouts if functions are used. If the Tool-Pac routines are used, you only need to be able to identify the ranges of the existing data.
ONE SAMPLE TESTS
MEAN
Name
μ
Mean
St. Dev
n
df
t
Probability
(1)
(2)
(3)
(4)
(5)
(6)
(7)
FOR A HYPOTHESIS ON ONE POPULATION MEAN (KNOWN), NORMALLY DISTRIBUTED, ONE TAIL TEST
POPULATION σ KNOWN
(1) From Claim
(2) Function AVERAGE of data
(3) From Claim
(4) Function COUNT on data
(5) Cell(4) - 1
(7) Function NORMDIST( (2), (1), (3), TRUE )
z = (⎯X − μ) / ( σ / √ n)
σ NOT KNOWN, LARGE SAMPLE
(1) From Claim
(2) Function AVERAGE on data
(3) From STDEV on data
(4) Function COUNT on data
(5) Cell(4) - 1
(7) Function NORMDIST( (2), (1), (3), TRUE )
z = (⎯X − μ) / ( s / √ n)
σ NOT KNOWN, SMALL SAMPLE
(1) From Claim
(2) Function AVERAGE on Data
(3) From STDEV on data
(4) Function COUNT on data
(5) = (4) – 1
(6) = ( ( (1) – (2) ) ) / ( (4) / SQR (3) )
(7) Function TDIST( (6), (5), 1 or 2 from Claim )
t = (⎯X − μ) / ( s / √ n)
FOR A HYPOTHESIS ON ONE POPULATION MEAN OR MEDIAN (KNOWN), NORMALLY DISTRIBUTED, TWO TAIL TEST
POPULATION σ KNOWN
Name
μ
Sigma
Probability
(1)
(2)
(3)
(1) From Claim
(2) From Claim
(3) Function ZTEST( range of data, (1), (2) )
POPULATION σ UNKNOWN, LARGE SAMPLE
Name
μ
Probability
(1)
(2)
(1) From Claim
(2) Function ZTEST( range of data, (1) ..leave blank.. )
MEDIAN
Name
Population Median
# of + signs
# of – signs
n
Smaller of a or b
z Value
Probability
(1)
(2)
(4)
(3)
(5)
(6)
(7)
FOR A HYPOTHESIS ON ONE POPULATION MEDIAN VALUE, NONPARAMETRIC, LARGE SAMPLES (N > 25)
(1) Function MEDIAN on data
(2) Manually count the number of data values greater than or equal to (1)
(3) Function COUNT on data
(4) = (3) – (2)
(5) Smaller of (2) or (4)
(6) = ( (5) + 0.5 – (3) / 2) / ( SQR(3) / 2 )
(7) Function NORMSDIST( (6) )
z = [ x + 0.5 – n / 2 ] / [ √ n / 2 ]
FOR A HYPOTHESIS ON PROPORTION
Name
p
q
n
np
nq
Number of successes
p-hat
z
Probability
(1)
(2)
(3)
(4)
(5)
(6)
(7)
(8)
(9)
FOR A HYPOTHESIS ON ONE POPULATION PROPORTION, LARGE SAMPLES WHERE NP>5 AND NQ>5
(1) From Claim
(2) = 1 – (1)
(3) From Claim
(4) = (1) * (3)
(5) = (2) * (3)
(6) From Claim
(7) = (6) / (3)
(8) = ( (7) – (1) ) / SQR( (1) * (2) / (3) )
(9) Function NORMSDIST( (8) )
z = (p-hat − p) / √ (p × q / n)
FOR A HYPOTHESIS ON VARIANCE
Name
Population σ2
Variance
n
df
Chi
Probability
(1)
(2)
(3)
(4)
(5)
(6)
FOR A HYPOTHESIS ON ONE POPULATION VARIANCE
(1) From Claim
(2) From VAR on data
(3) Function COUNT on data
(4) = (3) - 1
(5) = (4) * (2) / (1)
(6) Function CHIDIST( (5), (4) )
χ2 = ( n − 1 ) × s2 / σ2
FOR A HYPOTHESIS ON CORRRELATION COEFFICIENT
Name
Correlation Coefficient r
n
df
t
Probability
(1)
(2)
(3)
(4)
(5)
(1) Function CORREL(Range of x data, Range of y data) or from Claim
(2) Function COUNT(Range of x) or from Claim
(3) = (2) - 2
(4) = (1) / SQR( (1 – (1) * (1) ) / (3) )
(5) Function TDIST( (4), (3), 1 or 2 from Claim )
t = r / √ ( (1 – r2 ) / (n – 2) )
TWO SAMPLE TESTS
FOR A HYPOTHESIS ON TWO POPULATIONS
MEANS, TWO INDEPENDENT SAMPLES
(1) Range of data set 1
(2) Range of data set 2
(3) Tails, either 1 or 2
1 is for a one tailed test
2 is for a two tailed test
(4) Characteristics of the two data sets
1 is for paired data values, having equal numbers of values (no missing values)
2 is for the equal variance (homoscedastic) characteristic
3 is for the unequal variance (heteroscedastic) characteristic
(5) Probability = TTEST( (1), (2), (3), (4) )
(6)
DIFFERENCES, PAIRED DEPENDENT SAMPLES
t = (⎯d − μd ) / ( sd / √ n)
Equal Variance
t = {(⎯X1 −⎯X2 ) −(μ1 − μ2)} / sm
sm = sp × √ ( 1/ n1 + 1/ n2 )
sp = √ (pooled variance)
(pooled variance) = { (df × s2 )1 + (df × s2)2 } / dfTotal
Unequal Variance
t = {(⎯X1 −⎯X2 ) −(μ1 − μ2)} / sm
sm = √ { (s2 / n)1 + (s2 / n)2 }
PROPORTIONS, RANDOM INDEPENDENT LARGE SAMPLES
(n1p>5, n1q>5, n2p>5 and n2q>5)
Subscript
Name
p
q
n
np
nq
Number of successes
p-hat
SD
z
Probability
1
(1)
(2)
(3)
(4)
(5)
(6)
(7)
2
(8)
(9)
(10)
(11)
(12)
(13)
(14)
Combined
(15)
(16)
(17)
(18)
(19)
(20)
(21)
(1) From Claim
(2) = 1 – (1)
(3) From Claim
(4) = (1) * (3)
(5) = (2) * (3)
(7) From Claim
(8) = (6) / (3)
(8) From Claim
(9) = 1 – (1)
(10) From Claim
(11) = (1) * (3)
(12) = (2) * (3)
(13) From Claim
(14) = (6) / (3)
(17) = (3) + (10)
(18) = (6) + (13)
(15) = (18) / (17)
(16) = 1 – (15)
(19) = SQR( (15) * (16) * ( 1 / (3) + 1 / (10) ) )
(20) = ( (7) – (14) – (1) + (8) ) / (19)
(21) Function NORMSDIST ( (20) )
z = {(p-hat1 − p-hat2 ) – (p1 – p2 )} / σm
p = (x1 + x2) / (n1 + n2)
q = 1 – p
σm = √ { (p × q / n1 ) + (p × q / n2 }
VARIANCES, TWO INDEPENDENT SAMPLES For a hypothesis on two population variances
(1) Range of data set 1
(2) Range of data set 2
(3) Probability = FTEST( (1), (2) )
F = s21 / s22
The FTEST function can be used here, but it gives incorrect p values.
DATA ANALYSIS ROUTINE OUTPUTS
The data analysis routines output the following table:
df
Degrees of freedom
t Stat
Calculated t value
P(T<=t) one-tail P1 t Critical one-tail P(T<=t) two-tail P2 t Critical two-tail You have to translate the P1 and P2 values to determine the probability of the given hypothesis being true. The output table is confusing here. Hypothesis Symbol Actual Data Values B>A
Actual Values B=A
Actual Data Values BA
1 – p1
p1
p1
2
B>=A
1 – p1
p1
p1
3
B α: Fail to reject H0
ii. If Probability ≤ α: Reject H0
d. If the test is a true hypothesis test, the calculated p value is NOT reported, only the decision is reported.
e. Decision: Reject H0:
i. H0 is the claim: There is enough evidence at … to reject the claim of …
ii. Ha is the claim: There is enough evidence at … to support the claim of ...
f. Decision: Fail to Reject H0:
i. H0 is the claim: There is not enough evidence at … to reject the claim of …
ii. Ha is the claim: There is not enough evidence at … to support the claim of …
g. Changing a hypothesis “in mid stream” presents problems in assigning an appropriate p value under the Neyman-Pearson concept. This requires now an inductive approach to the problem, evaluating multiple hypotheses. This is beyond Excel. Goodman (1999) argues then that a Bayesian approach be taken, in which the likelihood ratios of each hypothesis on the data be calculated and combined with prior probability structures for obtaining the correct probability values. Excel does not have the capability to obtain likelihood ratios from a set of data.

Author : David Heiser [dheiser594@gmail.com]

Standardized Residuals

noreply@blogger.com (ddadmin) — Sat, 08 Jan 2011 06:47:00 +0000

The actual output table of standardized residuals from linear regression is the residual divided by the unbiased standard deviation of the residuals.

One argument is that since the LMS solution is based on the sum of the residuals being zero, the appropriate divisor of the sum of squares would be N rather than N-1. This may be Cryer’s complaint.

The mathematics on biased or unbiased estimates is not based on degrees of freedom arguments, but on asymptotic characteristics of the standard deviation value. The residuals computed do not constitute a true “population”, since the assumption of a LMS solution is that the data is a sample of a population.

My position is that the calculation is correct as it is in Excel.

Author : David Heiser [dheiser594@gmail.com]

Regression Normal Probability Plot

noreply@blogger.com (ddadmin) — Sat, 08 Jan 2011 06:44:00 +0000

LINEAR REGRESSION OUTPUTS:
In the Data Analysis Regression routine, an input box appears. At the bottom there are boxes to check if specific outputs are requested. They are:

Residuals
Standardized Residuals
Residual Plots
Line Fit Plots
Normal Probability Plots

Residuals: Gives three columns of data below the regression output information block. The first column is observation and is just a sequence number referring the sequence of input data values. The second column is the predicted Y value, and the third column is the difference between the actual data Y value and the calculated Y value, based n the regression coefficients listed.

Standardized Residuals: Gives a column of values, which come from the division of the residual by the standard deviation of the residuals. Use it to look for outliers.

Residual Plots: Gives numerous plots of the residual versus each of the X variables. If only one X is input, there is only one chart. The chart can be changed just like all other Excel charts. Use it to detect patterns, indicated deviations from the regression equation.

Line Fit Plots: Generates a chart with a plot of predicted Y values versus observed Y values. Use it to determine if the regression is a suitable fit over the entire range of the data.

Normal Probability Plots:
Two new columns of values are generated in the same space below the regression output. The first column is a calculated value from:
100 * ((sequence number/n) – (1/2*n)),
where sequence number is the numbers from 1 to n. The second column is the sorted observed Y values from lowest to highest. These columns bear no relationship to the residual columns described above, although they occupy the same row space. The chart is just a scatter plot of these two columns. It bears no relationship to normal distribution characteristics.
The intent was to generate a form of a Meier-Kaplan plot of the Y data to determine if the distribution of Y values is normal. If it is normal, then correlation coefficients may be more important then regression coefficients.
The more common application today of the term “Normal Probability Plot”, is to plot the residuals versus a z value (or cumulative normal percentile) derived from the normal probability distribution for the ranking location of the residual. This gives a visual look to determine if the residuals are actually normally distributed. The assumption of error (as residuals) being normally distributed is an important assumption. The probability values on the coefficients and coefficient confidence intervals are based on this assumption. If the plot is roughly a straight line, then the assumption is valid.

Author : David Heiser [dheiser594@gmail.com]

Polynomial Regression in Excel

noreply@blogger.com (ddadmin) — Mon, 09 Aug 2010 14:01:00 +0000

EXCEL 2000
CENTERING INPUT DATA:
In most cases, centering the data about the mean (an approximate mean is OK) will improve accuracy of the result. If the problem requires a polynomial beyond a cubic, centering will be the only way it can be done. The standard coefficient error values will not be the values obtained from fitting a polynomial to the original data. A similar process to convert centered standard coefficient error values back to direct standard coefficient error values has not yet been worked out.
1. The centered data set:
a. Build a worksheet with the Y, X data. Set column A as the Y data and columns B to {m+1}1, as the X data. Reserve row 1 for labels that identify each of the powers of the X variable.
b. In column A, row {n+3} enter =AVERAGE(A2:A{n+1}) . Formula copy across to column B.
c. Copy the cells in row 1 and paste then in row {n+4}
d. In column A row {n+5} put in the formula =A2-A${n+3}) and formula copy across to column B.
e. Formula copy the selected cells down to row {2n+4}.
f. The centered X, Y starting data will be columns A and B from rows {n+4} to {2n+5).
g. Generate the polynomial terms.
i. For quadratic: Starting with cell C{n+5} enter =B{n+5} * B{n+5}
ii. For cubic: Starting with cell D{n+5} enter =B{n+5} * C{n+5}
iii. For quadratic: Starting with cell E{n+5} enter =B{n+5} * D{n+5}
iv. Continue this scheme up to the highest power desired.
h. The range for the Y data will be A{n+4}:A{2n+5}.
i. The range for the X variables will be B{n+4}:{m+1}{2n+5}, where m is the highest power of the fitting polynomial.
2. Go into Data Analysis, select Regression. Set Y to the range listed in g. above and set X to the range listed in h. above. Set the labels box.
1 The {….} is a notation whose value has to be converted either to a row number or an alphabetic character corresponding to a column designation. n is the number of “points” or observations in the data set, and m is the number of variables in the data set.
3. Set the Regression output to begin to the left of the centered data.
CONVERTING FROM CENTERED COEFFICIENTS TO DIRECT COFFICIENTS:
1. For fitting polynomials to centered X and Y values, the calculations to convert coefficient values and the intercept to the original coordinate system are more complex. It is a translation of the polynomial from the centered X and Y values to the original coordinates. Algebra can be used to show how this is done. Each term involves a sign, a binomial coefficient, the mean value of the X to a power and the regression coefficient.
2. The following instructions are for any polynomial up to a power of 10.
a. Build up a table on a worksheet in Excel, from column A to column W and from row 1 to row 12. Columns A to M contain the basic information, and columns N to W contain the cells of the cross products.
b. Put in the following data into the cells.
i. Cell N1 is the average Y from the data worksheet Cell A{n+3}.
ii. Cell N3 is the average X from the data worksheet Cell B{n+3}
iii. Cells A1:A11 are the Data Analysis Regression output sheet cells corresponding to the column of fitted coefficient values. Put in a zero for any coefficients beyond the ones fitted.
iv. The center of the table represents the standard binomial coefficients. Column B is a sign control on the term.
v. In cells N4:N11, put in the indicated formula. These are the powers of the average X.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
1
Intercept
+1
1
0
0
0
0
0
0
0
0
0
0
average y
2
Coef A
-1
1
-1
0
0
0
0
0
0
0
0
0
1
3
Coef B
+1
1
-2
1
0
0
0
0
0
0
0
0
average x
4
Coef C
-1
1
-3
3
-1
0
0
0
0
0
0
0
M3*M3
5
Coef D
+1
1
-4
6
-4
1
0
0
0
0
0
0
M4*M3
6
Coef E
-1
1
-5
10
-10
5
-1
0
0
0
0
0
M5*M3
7
Coef F
+1
1
-6
15
-20
15
-6
1
0
0
0
0
M6*M3
8
Coef G
-1
1
-7
21
-35
35
-21
7
-1
0
0
0
M7*M3
9
Coef H
+1
1
-8
28
-56
70
-56
28
-8
1
0
0
M8*M3
10
Coef I
-1
1
-9
36
-84
126
-126
84
-36
9
-1
0
M9*M3
11
Coef J
+1
1
-10
45
-120
210
-252
210
-120
45
-10
1
M10*M3
12
M11*M3
vi. Put in the following formulas on the diagonals. Then formula copy down to row 11 in each column. It will not properly formula copy across a row.
vii. In cell O12, enter =SUM(O1:O11) and formula copy across to cell Y12. These cells will contain the translated direct coefficient values for the polynomial
O
P
Q
R
S
T
U
V
W
X
Y
1
A1+N1
2
A2*B2* C1*N3
A2*B2* D2*N2
3
A3*B3* C2*N4
A3*B3* D3*N3
A3*B3* E3*N2
4
A4*B4* E4*N3
A4*B4* F4*N2
5
A5*B5* F5*N3
A5*B5* G5*N2
6
A6*B6* G6*N3
A6*B6* H6*N2
7
A7*B7* H7*N3
A7*B7* I7*N2
8
A8*B8* I8*N3
A8*B8* J8*N2
9
A9*B9* J9*N3
A9*B9* K9*N2
10
A10*B10* K10*N3
A10*B10* L10*N2
11
A11*B11* L11*N3
A11*B11* M11*N2
12
SUM (O1: O11)
SUM (P2:P11)
SUM(Q3 :Q11)
SUM (R4:R11)
SUM (S5:S11)
SUM (T6:T11)
SUM (U7:U11)
SUM (V8:V11)
SUM(W9: W11)
SUM (X10:X11)
SUM (Y11:Y11)
c. Transfer the values in cells O12 to Y12 to the regression output sheet. The standard errors of the coefficients will remain the same.
FILIP POLYNOMIAL FIT
Centering the 82 observations and running Data Analysis – Regression on the data (as a multivariate regression) gave a set of parameter values. The coefficients were translated back to the original coordinate system using the method described above. Other parameters values came from the centered data run. The standard deviations of coefficient values in the original coordinate system cannot be recovered from the centered data standard deviations of coefficient values.
The comparison is given in LRE values.
Parameter Estimate Standard Deviation of Estimate
B0 10.28 0
B1 10.28 0
B2 10.27 0
B3 10.27 0
B4 10.26 0
B5 10.25 0
B6 10.24 0
B7 10.23 0
B8 9.25 0
B9 10.40 0
B10 10.20 0
Residual Standard Deviation 14.51
R-Squared 15.95
Analysis of Variance Table
Source df SS MS F ratio
Regression 16 14.83 14.80 14.33
Residual 16 14.27 14.18
EXCEL 2003:
The new regression algorithm is robust enough to do polynomial regression directly. No special methods are needed.

Author : David Heiser [dheiser594@gmail.com]

Singularity, Multicolinearity, Accuracy And Other Matrix Problems

noreply@blogger.com (ddadmin) — Fri, 06 Aug 2010 14:43:00 +0000

SINGULARITY
Singularity occurs when one or more columns (or rows) of the X matrix are either identical or differ by a constant times the other column or row. Near singularity occurs when the data in one row (or column) is very close to a multiple of another row (or column).
It is not an issue when there is only one X variable. It occurs when there are two or more X variables.
As Reyment and Joreskg (1996) say, “By finding the rank of a data matrix, much can be learned about the complexity of the contained data. The rank will give the dimensionality of the matrix, which determines the number of linearly independent vectors necessary to span the space containing the vectors of the matrix, The eigenvectors and eigenvalues of a matrix will not only determine the rank, but will also yield a set of linearly independent basis vectors”. From the basis vectors, a new X’X and X’Y matrix can be constructed which will give the correct regression of Y on the linearly independent X variables (which may not be directly related on a 1:1 basis with the identified variables)
The only adequate way to determine singularity and near singularity is to examine the eigenvalues. Excel does not have a function that will return a vector of eigenvalues for the X’X matrix. This is a real weakness in Excel. However, the interpretation of an eigenvalue vector with regard to singularity is generally beyond the scope of introductory statistics.
MATRIX SINGULARITY PROBLEMS:
EXCEL 2000
LINEST in Excel 2000 is not able to work when the input data has singularity. It will stop with meaningless values in the output. This is discussed in KBAs 209326 and 828533. Excel 2003 LINEST however is resistant to singularity problems (see KBA 828533) and when it occurs, it will automatically delete the singular columns from the analysis and reset the degrees of freedom to reflect this.
By doing CORREL on the data, columns that have correlation values of 0.999999 or greater, indicate a singular or near singular data input matrix. Excel 2000 matrix inversion is fairly robust to near singularity, and will calculate a linear regression with one pair of columns that correlate to 0.999999 or more. Excel 2003 LINEST will not find singularity when high correlation values occur. The relationship has to be exact (or when in the QR triangularization, the normalized diagonal value is smaller than 5E-15.)
The method given in the next paragraph is a weak method, but it is the only way to get some rank information out of the X matrix using only Excel provided functions. Excel does not have the capabilities to fix the problem.
Checking to see if there is a singularity problem:
1. Put the X matrix on a separate worksheet.
2. Create the transpose matrix of X using the TRANSPOSE matrix function (X’)
3. Form the X’X matrix by multiplying the transposed matrix times the original matrix using the MMULT matrix function (X’X).
4. Note: b and c can be done in one equation =MMULT(TRANSPOSE(B2:G17),B2:G17).
5. Create the inverse matrix of the X’X matrix using the MINVERSE matrix function. (X”X)-1 . If Excel gives an error code, then X is definitely singular. A singular symmetrical matrix here is the same as dividing by zero.
6. Obtain the determinant of both X”X and (X”X)-1 using the MDETERM function in a single cell. It returns a single value.
a. Let a = MDETERM(X’X) and b = MDETERM(((X”X)-1).
b. If the inversion matrix (X”X)-1 is correct, a should equal 1/b
c. The LRE value of the 1/b value with reference to the a value is an indicator of rank or singularity problems.
EXCEL 2003
The new algorithm in LINEST detects singularity, and automatically deletes the offending variable.
MULTICOLINARITY:
Co-linearity occurs when data columns are very closely correlated. That is the correlation coefficients are close to one, but not exactly one (plus or minus). If it were exactly one, it would be a case of singularity.
The inherent problem with co-linearity is that regression coefficient values become very dependent on the distribution and values of the errors. Given a reference column of data (and a value of a reference regression coefficient), by adding a new data column that is co-linear with the reference column, the resulting regression coefficients will spread away from the reference value, as co-linearity increases, with the sum of these two coefficients approaching the reference value asymptotically. The result with co-linearity is that some coefficient values that appear to be unrealistic. Co-linearity also affects the values of the other coefficients.
Co-linearity check:
1. Select an empty block of cells, m x m.
2. In the first cell enter =CORREL(B{n+5}:B{2n+5},B{n+5}:B{2n+5})
3. Formula copy across m cells.
4. Formula copy down m rows.
5. The block now contains the correlation coefficients of all variables, with a 1 down the diagonal and values above the diagonal equal to values below the diagonal.
6. Look for cells off the diagonal that have values close to 1. A value of 0.99 and larger, is considered close to1. Excel matrix inversion is fairly robust to near singularity, and will calculate a linear regression with one pair of columns that correlate to 0.999999 or more.
7. Coefficient values for variable closely correlated ( 0.99 and higher) variables may be “screwy” and have high standard errors. There is nothing you can do about this except to change your model to reduce the effects.
ESTIMATING THE ACCURACY OF THE RESULTS:
COMPUTING METHODS:
In general the methods to solve linear regression problems are:
Input matrix inversion and LU decomposition
QR decompositions, using Householder transformations and plane rotations
QR Gram-Schmidt Algorithm
Classical
Modified
LINEST in Excel 2000 uses input matrix inversions and LU decomposition. Excel 2003 uses a QR decomposition using Householders transformations to get a triangular matrix for solution of coefficient values.
Matrix arithmetic theory (see Stewart 1995 and 1998) goes into the deeper mathematical aspects. It is based on the concept of matrix norms (every non-singular matrix has a norm, and there are many types of norms). The errors in regression coefficients due to the finite arithmetic used, is reflected in the concept that the actual output results are different from exact arithmetic results. This difference is unknown since exact arithmetic is not possible, but by applying theory here, the actual computer results can be expressed as being equal to exact arithmetic on an augmented matrix that differs from the true input matrix(s). Theory allows estimates to be made of the differences between the actual matrix and the augmented matrix in terms of norm values.
ESTIMATING ACCURACY (EXCEL 2000)
The basis is that the accuracy of the solution depends on the accuracy of the inversion of the X’X covariance matrix. The figure of merit used is the LRE value of the determinant of the (X’X)-1(X’X) matrix. If the inversion is precise, this (hat) matrix is the identity matrix, and the identity matrix has a determinant of one. If the off-diagonal values are large enough, the determinant will be different from one. The LRE value of the difference from one is a measure of the accuracy of the regression results. The process of obtaining the LRE value of the determinant is given below.
1. Put the X matrix on a separate worksheet.
2. Create the transpose matrix of X using the TRANSPOSE matrix function (X’)
3. Form the X’X matrix by multiplying the transposed matrix times the original matrix using the MMULT matrix function (X’X).
4. Note: b and c can be done in one equation =MMULT(TRANSPOSE(B2:G17),B2:G17) /goes in cells B25:G30/
5. Create the inverse matrix of the X’X matrix using the MINVERSE matrix function. (X”X)-1 . If Excel gives an error code, then X is definitely singular. A singular symmetrical matrix here is the same as dividing by zero.
6. Create the product matrix by multiplying the inverse matrix (X”X)-1 by (X’X) using the MMULT matrix function (Q).
7. Note: d and e can be done in one equation.
8. =MMULT(MINVERSE(B25:G30),B25:G30) /goes in cells B35:G40)/
9. Examine Q for the magnitude of the off diagonal cells, and how close the diagonal cells are to 1.
10. If the matrix looks to be reasonably close to a unity matrix, continue and get the determinant of the Q matrix.
11. Obtain the determinant using the MDETERM function in a single cell. It works on a symmetrical matrix and returns a single value.
12. Calculate the LRE value with reference to 1.0.
There are no guidelines on what is an acceptable LRE value here. It really depends of how well the linear equation model fits the data.
This limit is not a hard limit, since larger matrices will have larger off diagonal terms. This is due to the limitations of IEEE 64 bit floating point arithmetic. The determinant of the (X’X)-1 * (X’X) product when given an LRE calculation with reference to 1, is a crude estimate of the LRE value of the coefficients.
For example from the Longley data set: The additive was applied to the centered data.
Table N-1: Coefficient and Determinant LRE Values
Variable
Original
Centered
1000 Additive
100,000 Additive
10,000,000 Additive
Intercept
8.26
12.41
-
-
-
GNP Deflator
7.57
12.80
8.03
3.76
0
Gross National Product
7.78
13.36
9.24
4.13
0.68
Unemployment
8.36
13.61
9.61
4.75
1.40
Military Employment
8.60
12.49
9.83
5.01
1.69
Population
7.40
13.17
8.86
3.56
0
Year
8.27
13.17
9.26
4.87
1.76
Determinant
10.83
13.55
9.62
5.12
3.60
The determinant LRE value runs somewhat higher than the LRE values of the coefficients. For a rule-of-thumb, it would suggest that if the LRE value of the
determinant is less than 5, the coefficient vector should be considered inaccurate and not be used.
ESTIMATING ACCURACY (EXCEL 2003)
Under Excel 2003, there is no way to estimate the accuracy of the results from LINEST. Stewart making some general observations says that generally the QR methods tend to give more accurate results but the difference from normal equations (the edge) is small. However the QR methods are much more stable. There are conditions where the normal equations are not even positive definite. (Stewart, 1995, p 318)
Excel 2000 uses the normal equations. Excel 2003 uses a QR method. However for the example given in KBA 828533, internal values to 15 digits are given with regard to applying LINEST 2003 to a simple problem. Stewarts (1995) QR algorithm 1.11 gives identical internal values.

Author : David Heiser [dheiser594@gmail.com]