Linest Function

Purpose

Return value

Syntax

=LINEST(known_ys,[known_xs],[const],[stats])

• known_ys - An array or range of dependent y values.
• known_xs - [optional] An array or range of independent x values.
• const - [optional] Boolean - normal or force the constant b to equal 0. Default is TRUE = normal calculation.
• stats - [optional] Boolean - return additional statistics. Default is FALSE = slope and intercept only.

Using the LINEST function

The LINEST function returns statistics for a best fit straight line through supplied x and y values. The values returned by LINEST include slope, intercept, standard error values, and more, up to 10 different statistics in total. To find the best fit of a line to the data, the LINEST function uses the “least squares” method, the standard approach in regression analysis.

The LINEST function returns more than one value at a time in an array . In its most basic form, LINEST returns just intercept and slope. Optionally, LINEST can also return 10 separate statistics for the regression analysis as shown in the worksheet above. In Excel 365 , which supports dynamic arrays , the array of values will spill into cells in the worksheet automatically. In other versions of Excel, you must enter the LINEST as a multi-cell array formula to see all values.

Available Statistics

The table below shows the statistics that can be returned by the LINEST function. Note the first two, slope and intercept are returned by default. The other statistics are returned by setting the stats argument to TRUE. When all statistics are returned, they are delivered in a 2D array , 5 rows by 2 columns. In the worksheet shown above, the range F4:G8 shows the order in which statistics are returned.

Examples

By default, LINEST returns just two statistics, slope and intercept. For example:

=LINEST({1.8;5.3;8.2;12;13.5},{1;3;5;7;8}) // default

returns a 1 row by 2 column array like this:

{1.6726,0.1317}

Setting the stats argument to TRUE or 1 will cause LINEST to return all 10 statistics:

=LINEST({1.8;5.3;8.2;12;13.5},{1;3;5;7;8},TRUE,TRUE) // more stats

The result is an array with 5 rows and 2 columns:

{1.6726,0.1317;
0.0371,0.2017;
0.9985,0.2124;
2034.443,3;
91.7567,0.1353}

Note: values above have been rounded to make them easier to read.

Purpose

Return value

Syntax

=MAX(number1,[number2],...)

• number1 - Number, reference to numeric value, or range that contains numeric values.
• number2 - [optional] Number, reference to numeric value, or range that contains numeric values.

Using the MAX function

The MAX function returns the largest numeric value in the data provided. The MAX function can be used to return the largest value from any type of numeric data. For example, MAX can return the slowest time in a race, the latest date, the largest percentage, the highest temperature, or the top sales number.

The MAX function takes multiple arguments in the form number1 , number2 , number3 , etc., up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MAX ignores empty cells, text values, and the logical values TRUE and FALSE.

Basic Example

The MAX function returns the largest numeric value in the supplied data:

=MAX(12,17,25,11,23) // returns 25

When given a range , MAX returns the largest value in the range:

=MAX(A1:A10) // maximum value in A1:A10

Mixed arguments

The MAX function can accept a mix of arguments:

=MAX(5,10)
=MAX(A1,A2,A3)
=MAX(A1:A10,100)
=MAX(A1:A10,C1:C10)

Logical values

The MAX function will ignore logical values and numbers entered as text that appear on the worksheet. However, if such values are provided directly as arguments , MAX will use them:

=MAX(-1,TRUE) // returns 1
=MAX(-1,TRUE,"3") // returns 3

Errors

When MAX encounters an error in a range, it will return an error. To calculate a maximum value while ignoring errors , you can use the AGGREGATE function , which can be configured to ignore errors. See a detailed example here .

Other functions

Excel provides other functions that deal with maximum values and rank:

• To calculate the maximum value with criteria, use the MAXIFS function .
• To retrieve the nth largest value in a data set, use the LARGE function .
• To determine the rank of a number in a set of data, use the RANK function .

Notes

• Arguments can be provided as numbers, names, arrays, or references.
• MAX accepts up to 255 arguments. If arguments contain no numbers, MAX returns 0.
• MAX ignores empty cells, text values, and TRUE and FALSE in references.
• MAX will evaluate numbers as text and logical values supplied directly as arguments.
• To include logical values in a reference, see the MAXA function .