Purpose

Return value

Syntax

=HARMEAN(number1,[number2],...)
  • number1 - First value or reference.
  • number2 - [optional] Second value or reference.

Using the HARMEAN function

The Excel HARMEAN function returns the harmonic mean for a set of numeric values. The harmonic mean is a kind of numeric average, calculated by dividing the number values in a list by the sum of the reciprocal of each value. In other words, the harmonic mean is the reciprocal of the average of the reciprocals.

Because the harmonic mean tends toward the smallest values in a set of data, it limits the impact of large outliers, but exaggerates the impact of small outliers. The harmonic mean is always less than the geometric mean ( GEOMEAN ), which is always less than the arithmetic mean ( AVERAGE ).

The HARMEAN 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. Often, a single range or array is used instead of multiple arguments, as seen in the example worksheet.

Examples

The average of 1, 2, and 6 is 3. The harmonic mean of 1, 2, and 6 is 1.8:

=AVERAGE(1,2,6) // returns 3
=HARMEAN(1,2,6) // returns 1.8

In the example shown, the formulas in E5 and E6 are, respectively:

=AVERAGE(B5:B14)
=HARMEAN(B5:B14)

Note that harmonic mean reduces the impact of the larger outliers in the data set.

Notes

  • Arguments can be numbers, names, arrays, or references that contain numbers.
  • Empty cells, and cells that contain text or logical values are ignored.

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