How to use the AVERAGEA function
What is the AVERAGEA function?
The AVERAGEA function returns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to 1.
What is the average?
The average is a way to calculate central tendency which is the place of the center of a set of numbers in a statistical distribution. The most often used measures to calculate central tendency are:
- Average - arithmetic mean.
- Median - the middle number of a group of numbers.
- Mode - the most frequent item in a group
The average is the arithmetic mean.
What is arithmetic mean?
The arithmetic mean is calculated by dividing the sum of all values by the number of values.
For example, an array contains these values: 3,2,1
The sum is 3 + 2 + 1 equals 6
The number of values is 3.
6/3 equals 2. The average of 3, 2, 1 is 2
What are the differences between arithmetic mean, harmonic mean and geometric mean?
The arithmetic, harmonic, and geometric means are different ways to average data:
Arithmetic mean is calculated by summing values and dividing by count.
Harmonic mean is found by reciprocating values, averaging, then reciprocating.
Geometric mean is calculated by multiplying values and taking the nth root.
When to use:
- Arithmetic mean for typical or normally distributed data
- Harmonic mean for rates and ratios
- Geometric mean for percentages and proportional growth
The choice depends on data characteristics and what the average should represent. Arithmetic mean is most common, while harmonic and geometric means have specialized uses.
What are boolean values?
Boolean values are TRUE and FALSE. The numerical equivalent to TRUE is 1 and FALSE is 0 (zero). Boolean values are generated when a logical expression is evaluated, for example 1=1 returns TRUE where 1="A" returns FALSE.
What is the difference between AVERAGEA function and the AVERAGE function?
The AVERAGEA function includes logical values and text representations of numbers in a reference as part of the calculation.
For example, a cell range contains these vales: 3, FALSE,2, and 1
AVERAGE(3, FALSE,2, 1) returns 2.
3+2+1 = 6
6/3 = 2
AVERAGEA(3, FALSE,2, 1) returns 1.5.
3+0+2+1 = 6
6/4 = 1.5
The AVERAGEA function evaluates 1 for boolean value TRUE and 0 (zero) for FALSE. Text values and formulas that return nothing "" evaluate to 0 (zero).
This table shows the difference between AVERAGEA and AVERAGE functions.
Values | AVERAGEA | AVERAGE |
---|---|---|
Text values | 0 | Ignored |
Empty cells | Ignored | Ignored |
TRUE | 1 | Ignored |
FALSE | 0 | Ignored |
Related AVERAGEA functions
Function | Description |
---|---|
GEOMEAN(number1, [number2], ...) | Returns the geometric mean of values |
HARMEAN(number1, [number2], ...) | Returns the harmonic mean of values |
AVERAGE(number1, [number2], ...) | Returns the arithmetic mean of values |
AVERAGEA(value1, [value2], ...) | Returns arithmetic mean ignoring text |
AVERAGEIF(range, criteria, [average_range]) | Conditional arithmetic mean if criteria met |
AVERAGEIFS(average_range, criteria_range1, criteria1, ...) | Conditional arithmetic mean on multiple criteria |
AVERAGEA function Syntax
AVERAGEA(value1, [value2], ...)
AVERAGEA function Arguments
value1 | Required. Values for which you want to calculate the average. |
[value2] | Optional. Up to 254 additional arguments. |
AVERAGEA function example
Formula in cell D3:
0 + 1 + 2 + 1 + 0 = 4/5 = 0.8
AVERAGEA function not working
Functions in 'Statistical' category
The AVERAGEA function function is one of 74 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form