Microsoft Office Tutorials and References

In Depth Information

**Syntax**

a function returns several values, you must enter the function by using

Ctrl+Shift+Enter. You should also select a large enough range in advance

before entering the formula. Figuring out the size of the range in advance is

difficult because it varies, depending on the shape of the independent vari-

ables and also if you ask for statistics.

Note

Note that y, x, and m can be vectors. The array that LINEST returns

is backward from what you would expect. The slope for the last in-

dependent variable appears first: {mn,mn-1,...,m1,b}. LINEST can also

return additional regression statistics.

However, LINEST is far more powerful than SLOPE and INTERCEPT. Addi-

tional arguments available in LINEST are not available in the easier func-

tions.

Syntax

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

The LINEST function calculates the statistics for a line by using the least-

squares method to calculate a straight line that best fits the data, and

it returns an array that describes the line. Because this function returns

an array of values, you must enter it as an array formula with

Ctrl+Shift+Enter. The equation for the line is y = mx + b or y = m1x1 + m2x2 +

... + b (if there are multiple ranges of x values) where the dependent y value

is a function of the independent x values. The m values are coefficients cor-

responding to each x value, and b is a constant value.

The LINEST function takes the following arguments:

•
known_y's

known_y's
—
This is the set of y values you already know in the re-

lationship y = mx + b. If the array known_y'sis in a single column,

each column of known_x'sis interpreted as a separate variable. If

the array known_y'sis in a single row, each row of known_x'sis in-

terpreted as a separate variable.

•
known_x's

known_x's
—
This is an optional set of x values that you might

already know in the relationship y = mx + b. The array known_x'scan

include one or more sets of variables. If only one variable is used,