Documentation/Calc Functions/AVERAGEA
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
AVERAGEA
Category:
Statistical Analysis
Summary:
Calculates the average (arithmetic mean) of a set of data points.
AVERAGEA is a variant of the AVERAGE function. The main difference between the two is that AVERAGE ignores cells containing text, whereas AVERAGEA treats text values as the number 0.
Syntax:
AVERAGEA(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 AVERAGEA 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:
- Empty cells are ignored.
- Text strings are treated as the number 0.
- If there are no supplied numbers, then AVERAGEA reports a #DIV/0! error.
- If only one value is supplied, then AVERAGEA returns that value.
Additional details:
- The formula for AVERAGEA is:
- [math]\displaystyle{ \text{AVERAGEA} ~=~ \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.
- AVERAGEA 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 |
---|---|---|
=AVERAGEA(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 =AVERAGEA(A1; A2; A3; A4; A5) and =AVERAGEA({1, 2, 3}; {4, 5}) |
3 |
=AVERAGEA(1.5; -2.0; 0.0; -4.2) | Here the function calculates the average as -1.175. | -1.175 |
=AVERAGEA(1; "Some string"; 5) | Here the function calculates the average of the values 1, 0, and 5, returning 2. | 2 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
AVERAGEA