A logical values used to determine whether the constant b is equal to 0.

And If the Const argument is equal to TRUE or omitted, the Constant b is calculated.

A logical value used to determine whether the function need to return additional regression statistics.

If this argument is TRUE, the LINEST function will return additional regression statistics, otherwise, The LINEST function will return only the m-coefficients and the constant b.

The value of.595 agrees with the value returned by LINEST() in cell G5, and by the ratio of the sums of squares in cell G13.

This paper touches only lightly on the question of whether it is appropriate to adopt the option: There are well reasoned arguments on each side of the issue.

The error was not corrected until Excel 2003, and it remains in Excel 2010, in the values of R 2 that can be displayed with chart trendlines.

If const is TRUE or omitted, the constant is calculated and included.

If const is FALSE, the constant is omitted from the equation.

And there is a real question of whether the const argument is a useful option at all.

It extends to the whole area of regression analysis, regardless of the platform used to carry out the regression.

That belief is based on a higher value for R 2, and an F ratio that argues more strongly for rejecting a null hypothesis of no relationship between the Y variable and the X composite.

This (spurious) conclusion is based on a misunderstanding of what happens mathematically when the constant is forced to zero.

The first set of results in F3:I7 is based on a constant calculated normally ( const TRUE ).

The second set of results in F10:I14 is based on a constant that is forced to equal zero ( const FALSE ).

They are based on the predicted Y values, in L21:L40, and the deviations of the predicted values from the actuals, in M21:LM40.

One useful way to calculate that figure (and a useful way to think of it) is.

The result, 0.595, states that 59.5 of the variability in the Y values is attributable to variability in the composite of the X values.

The former are calculated using Excels matrix functions; the latter are calculated using the LINEST function.

Its all as is expected according to the mathematics underlying regression analysis.

They are both much larger than the sums of squares reported in Figure 2.

The reason is that the deviations that are squared and summed in Figure 3 are the differences between the values and zero, not between the values and their mean.

It can happen that the sum of squares regression gets larger relative to the sum of squares residual, and the result is to increase the apparent value of R 2.

Using the sums of squares shown in Figure 2 and Figure 3, for example.

But does it really mean that the regression equation thats returned in Figure 3 is more accurate than the one returned in Figure 2 After all, the square root of R 2 is the multiple correlation between the actual Y values and the composite, predicted Y values.

The higher that correlation, the more accurate the prediction.

There, the multiple R is calculated at.772, and the multiple R 2 is calculated at.595 (cells H22 and H23).

How To Do Linest In Excel For Mac