Large Function

Purpose

Return value

Syntax

=LARGE(array,k)

• array - An array or range of numeric values.
• k - Position as an integer, where 1 corresponds to the largest value.

Using the LARGE function

The LARGE function returns a numeric value based on its position in a list when sorted by value. In other words, LARGE returns the “nth largest” value in the list where 1 corresponds to the largest value, 2 corresponds to the second largest value, etc. For example, the LARGE function is useful when you want to retrieve first, second, or third highest scores for a test.

The LARGE function takes two arguments , array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the largest value in array , provide 1 for k . To return the fifth largest value in array , provide 5 for k .

To get nth smallest values, see the SMALL function .

Examples

In the formula below, the LARGE function returns the third largest value in a list of five numbers provided in an array constant :

=LARGE({29,14,33,19,17},3) // returns 19

Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd largest values in a range:

=LARGE(range,1) // 1st largest value
=LARGE(range,2) // 2nd largest value
=LARGE(range,3) // 3rd largest value

In the example shown, the formulas in G5, G6, and G7 are, respectively:

=LARGE(D5:D16,1) // returns 92
=LARGE(D5:D16,2) // returns 89
=LARGE(D5:D16,3) // returns 86

See below for more advanced formulas based on the LARGE function.

Notes

• LARGE ignores empty cells, text values, and TRUE and FALSE values.
• If array contains no numeric values, LARGE returns a #NUM! error.
• To determine the rank of a number in a data set, use the RANK 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.