Description
Use this function to calculate the average of values in a range, including numbers, text, and logical values. Supported in Chains. Can be used with CHILDREFS.
AVERAGEA is useful for calculating averages in datasets that contain a mix of numeric and non-numeric values, such as performance ratings that include both scores and text evaluations.
Syntax
AVERAGEA(value_1, […, value_254])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
value_1 |
Yes | The first number, cell reference, or range for which you want the average. | Numbers, text, logical values (TRUE/FALSE), cell references, or a formula which results in any of these. |
value_n
|
No | Additional numbers, cell references, or ranges (up to 254 additional arguments). | Numbers, text, logical values (TRUE/FALSE), cell references, or a formula which results in any of these. |
Example
Sample data
| A | B | C | D |
|---|---|---|---|
| Employee | Performance | Score | Bonus Eligible |
| John | Excellent | 5 | TRUE |
| Sarah | Good | 4 | TRUE |
| Mike | Average | 3 | FALSE |
| Emily | Poor | 1 | FALSE |
| David | TRUE |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=AVERAGEA(C2:C7) |
Calculates the average of all scores, including empty cells. | 2.6 |
=AVERAGEA(B2:B7) |
Calculates the average of performance ratings, treating text as 0. | 0 |
=AVERAGEA(D2:D7) |
Calculates the average of bonus eligibility, treating TRUE as 1 and FALSE as 0. | 0.6 |
=AVERAGEA(B2:D7) |
Calculates the average across all data types in the range. | 1.0667 |
=AVERAGEA(5, "Text", TRUE, FALSE) |
Calculates the average of mixed data types. | 1.5 |
Notes
- If the range contains no numeric values, AVERAGEA returns the #DIV/0! error.
- AVERAGEA is particularly useful when you need to include logical values (TRUE/FALSE) in your average calculation.
-
AVERAGEA differs from AVERAGE in how it treats non-numeric values:
- Text and FALSE are evaluated as 0
- TRUE is evaluated as 1
- Error values and empty cells are ignored and not counted in the calculation.
Tips
- Use AVERAGEA when you need to include logical values in your average calculation.
- Be aware of how AVERAGEA treats text and logical values to avoid misinterpretation of results.
- Be cautious when using AVERAGEA with text data, as it may lead to unexpected results due to text being treated as 0.
- When working with purely numeric data, use AVERAGE instead.
- Use AVERAGEA in conjunction with other functions such as COUNTA for comprehensive data analysis of mixed data types.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA