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 | |
|---|---|---|---|---|
1 |
Employee | Performance | Score | Bonus Eligible |
2 |
John | Excellent | 5 | TRUE |
3 |
Sarah | Good | 4 | TRUE |
4 |
Mike | Average | 3 | FALSE |
5 |
Emily | Poor | 1 | FALSE |
6 |
David | TRUE |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Average a range of scores that includes a blank cell. | =AVERAGEA(C2:C6) |
Averages the five scores (5, 4, 3, 1, and David's blank, counted as 0). Result: 2.6 |
| Average a range of text ratings, since text is always treated as 0. | =AVERAGEA(B2:B6) |
Treats every performance rating ("Excellent," "Good," etc.) as 0, since AVERAGEA can't interpret text as a number. Result: 0 |
| Average a range of logical (TRUE/FALSE) values. | =AVERAGEA(D2:D6) |
Treats TRUE as 1 and FALSE as 0, then averages the five bonus-eligibility values. Result: 0.6 |
| Average a rectangular range that mixes numbers, text, and logical values. | =AVERAGEA(B2:D6) |
Combines the Performance, Score, and Bonus Eligible columns, treating text as 0 and TRUE/FALSE as 1/0. Result: ≈1.0667 |
| Average values typed directly into the formula, mixing a number, text, and logical values. | =AVERAGEA(5, "Text", TRUE, FALSE) |
Treats "Text" as 0 and TRUE/FALSE as 1/0, then averages all four literal values. Result: 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.
- Empty cells are evaluated as 0.
- TRUE is evaluated as 1.
- Error values 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.