Microsoft Office Tutorials and References

In Depth Information

**LINEST**

Figure 11.23

LARGE
returns the

position of the specified

largest value in the data

set. To return the

corresponding state, use

the
VLOOKUP
function.

To return the smallest

value, use the
SMALL

function.

LINEST

LINEST
calculates a straight line that best fits your data, using the “least squares” method.

=LINEST(known_y’s,known_x’s,const,stats)

The
LINEST
function uses the equation

y = mx + b

If there are multiple ranges of x values the equation is

y = m1x1 + m2x2 + ... + b

Because the result is an array, the formula must be
array entered
by using Ctrl+Shift+Enter,

instead of just pressing Enter (see Figure 11.24).

The known values for the known data points; the dependent variable.

KNOWN_Y’S

The known data points. The independent variable. An optional argument. If

omitted assumed to be the array {1,2,3…} the same size as the
known_y’s
.

KNOWN_X’S

TRUE or FALSE indicating whether the y-intercept (b) is = 0 (FALSE) or

not (TRUE).

CONST

If stats is TRUE,
LINEST
returns the additional regression statistics, so

the returned array is {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r2,sey; F,df;

ssreg,ssresid}. If stats is FALSE or omitted,
LINEST
returns only the

m-coefficient and the constant b.(See Table 11.1.)

STATS

Before you start the function If stats will be FALSE, select a range of cells one

cell high and two cells wide (as in this example). If stats will be TRUE, select a range of

cells five cells high and two cells wide.
■

NOTE