Purpose

Return value

Syntax

=INTERCEPT(known_ys,known_xs)
  • known_ys - An array or range of numeric data points (dependent values).
  • known_xs - An array or range of numeric data points (independent values).

Using the INTERCEPT function

The INTERCEPT function returns the point at which a line will intersect the y-axis based on known x and y values. The intercept point is based on a regression line plotted with known x and y values. A regression line is a line that best fits that known data points. Use the INTERCEPT function to calculate the value of a dependent variable when the independent variable is zero (0).

The INTERCEPT function takes two arguments , known_ys and known_xs , which should be a range or array of numeric values. The known_ys represent dependent values and known_xs represent independent values. Both arguments must contain the same number values, or INTERCEPT will return #N/A.

Example

Values can be entered directly in INTERCEPT as array constants :

=INTERCEPT({2;0},{-1;1}) // returns 1

Typically, values are supplied as ranges. In the example shown, the formula in E5 is:

=INTERCEPT(C5:C9,B5:B9) // returns 2

This formula returns 2, based on known_ys in C5:C9, and known_xs in B5:B9.

Equation

The equation for the intercept of the regression line (a) is:

where b is the slope. The formula used by Excel to calculate slope is the same one used by the SLOPE function :

In the example shown, the intercept formula can be manually created like this:

=AVERAGE(C5:C9)-SLOPE(C5:C9,B5:B9)*AVERAGE(B5:B9)

This formula returns the same result as that returned by the INTERCEPT function.

Notes

  • If there is only one set of points, INTERCEPT will return #DIV/0!
  • If the count of known_ys is different from known_xs , INTERCEPT returns #N/A

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 .