Formulas and Functions

Chapter 10 Statistical Functions 265
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26
“Pasting from Examples in Help” on page 41
LINEST
The LINEST function returns an array of the statistics for a straight line that best ts the
given data using the “least squares” method.
LINEST(known-y-values, known-x-values, nonzero-y-intercept, more-stats)
 known-y-values: The collection containing the known y values. known-y-values is
a collection containing number values. If there is only one collection of known x
values, known-y-values can be any size. If there is more than one collection of known
x values, known-y-values can be either one column containing the values or one row
containing the values, but not both.
 known-x-values: An optional collection containing the known x values. known-
x-values is a collection containing number values. If omitted, it will be assumed to
be the set {1, 2, 3…} of the same size as known-y-values. If there is only one set of
known x values, known-x-values, if specied, should be the same size as known-y-
values. If there is more than one set of known x values, each row/column of known-
x-values is considered to be one set and the size of each row/column must be the
same as the size of the row/column of known-y-values.
 nonzero-y-intercept: An optional value specifying how the y intercept (constant b)
should be calculated.
normal (1, TRUE, or omitted): The value of the y intercept (constant b) should be
calculated normally.
force 0 value (0, FALSE): The value of the y intercept (constant b) should be forced
to be 0.
 more-stats: An optional value specifying whether additional statistical information
should be returned.
no additional stats (0, FALSE, or omitted): Do not return additional regression
statistics in the returned array.
additional stats (1, TRUE): Return additional regression statistics in the returned
array.
Usage Notes
The values returned by the function are contained in an array. One method of Â
reading the values in the array is to use the INDEX function. You can wrap the
LINEST function within the INDEX function: =INDEX(LINEST(known-y-values, known-
x-values, const-b, stats), y, x) where y and x are the column and row index of the
desired value.