Jump to content

Documentation/Calc Functions/AVERAGE

From The Document Foundation Wiki

Function name:

AVERAGE

Category:

Statistical Analysis

Summary:

Calculates the average (arithmetic mean) of a set of data points.

Syntax:

AVERAGE(Number 1 [; Number 2 [; … [; Number 255]]])

Returns:

Returns a real number, which is the arithmetic mean of the supplied data points.

Arguments:

Number 1, Number 2, … , Number 255 give the set of real numbers for which the average is to be calculated. Each argument may take one of the following forms:

  • A real number, or an expression that evaluates to a real number.
  • A reference to a single cell containing a real number.
  • A simple reference to a cell range containing real numbers (for example, A1:B9).
  • The name of a named range, comprising cells containing real numbers.
  • The name of a database range, comprising cells containing real numbers.
  • An inline array of real numbers (for example, {1.1, 2.2, 3.3, 4.4}).

Note that although AVERAGE can accept up to 255 arguments, each argument could specify a range of cells. This means that the number of real numbers processed could be many more than 255.

The following conditions (including errors) may be encountered:

  • If any argument is a string in quotation marks, then AVERAGE reports a #VALUE! error.
  • Text in cells and empty cells are ignored.
  • If there are no supplied numbers, then AVERAGE reports a #DIV/0! error.
  • If only one value is supplied, then AVERAGE returns that value.

Additional details:

  • The formula for AVERAGE is:
[math]\displaystyle{ \text{AVERAGE} ~=~ \frac{1}{n}\:\sum_{i=1}^{n} x_i }[/math]
where [math]\displaystyle{ x_i }[/math] is the i-th value and [math]\displaystyle{ n }[/math] is the total number of values.
  • AVERAGE benefits from its use of a Kahan summation algorithm which attempts to reduce the numerical error that can arise when adding a sequence of finite-precision floating-point numbers.

Examples:

Formula Description Returns
=AVERAGE(A1:A5) where cells A1:A5 contain the values 1, 2, 3, 4, and 5 respectively. Here the function calculates the average as 3. The same result is obtained from the formulas =AVERAGE(A1; A2; A3; A4; A5) and =AVERAGE({1, 2, 3}; {4, 5}) 3
=AVERAGE(1.5; -2.0; 0.0; -4.2) Here the function calculates the average as -1.175. -1.175

Related LibreOffice functions:

AVERAGEA

COUNT

SUM

ODF standard:

Section 6.18.3, part 2

Related (or similar) Excel functions:

AVERAGE