# Quant analytics: Using Excel LINEST() function for multivariate regression

Written by Administrator Monday, 14 March 2011 20:41

Quant analytics: Using Excel LINEST() function for multivariate regression

An example of data from an antique clock auction

# Obs Y Price($) X2 Age X3 Bids

1 1235 127 13

2 1080 115 12

3 845 127 7

4 1552 150 9

5 1047 156 6

31 1356 194 5

32 1262 168 7

Y Price is independent variable as price of antique clock. X2 Age is age of clock. X3 # Bids makes it no longer a simple regression. X2 & X3 are dependent variables or explanatory. THE more bidders or older the clock, the higher the price.

Use Excel LINEST() returns the array for the multiple regression.

Legend to Output Below

b1 b2 b3

se(b1) se(b2) se(b3)

R^2 se(y)

F d.f. <-degrees of freedom

ESS RSS <- Explained sum of square result and Residual sum of squared

Regression output (see below of Linest())

X3 X2 intercept

85.764 12.741 -1336.049 <- coefficients see below

8.803 .0912 15.272 <-this row is standard error

0.891 134.608 #n/a <-see legend above

.891 134.608 #n/a

R^2 is coefficient of determination so. 89 is quite high

se(y) is standard error of y/regression/or estimate

3 standard errors because it is multivariate

You need to select a range. Use =linest(known Y's, Known X's (both X2&X2 above),,true) Press Shift Enter -> Excel returns the array in the range you select

The return result are the coeffecients in the order of x3 x2 intercept as above in Regression output. This is specified right to left. The intercept is for the regression line. X2 would be for the agre and X3 would be for # of bids. Also would be

y=-1336.049*12.741*x2+85.764*x3 <- formula for the multiple regression

true for statistics note no contant set in 3rd option

Because you choose y and both x's, it becomes a multivariate regression not just x&y.

To calculate Critical T value (or Critical T stat)=85.764/8.802 = 9.73437092 >2 which statistically significant

http://www.youtube.com/watch?v=WAXv9y1wIL4