The AVERAGEA function in Excel is an advanced averaging function that goes beyond simple numbers. Unlike the standard AVERAGE function, AVERAGEA includes text values and logical values (TRUE/FALSE) in its calculations.
This makes AVERAGEA especially useful when working with mixed datasets such as survey results, system outputs, or performance indicators.
What Is the AVERAGEA Function?
AVERAGEA calculates the average of a range of values while evaluating each type of value as follows:
- Numbers – Included as-is
- TRUE – Treated as 1
- FALSE – Treated as 0
- Text values – Counted as 0
- Blank cells – Ignored
AVERAGEA Function Syntax
=AVERAGEA(value1, [value2], ...)Arguments
- value1 – Required. First value, cell, or range.
- value2, … – Optional. Additional values or ranges.
Download the AVERAGEA Excel Template
⬇ Download AVERAGEA_Template.xlsx
This Excel file demonstrates how AVERAGE and AVERAGEA treat
numbers, text values, logical values (TRUE/FALSE), and blank cells.
Example: AVERAGEA with Mixed Data
| Value | Explanation |
|---|---|
| 10 | Number |
| 20 | Number |
| TRUE | Counts as 1 |
| FALSE | Counts as 0 |
| Text | Counts as 0 |
Formula:
=AVERAGEA(A2:A6)Excel converts the values to:
10, 20, 1, 0, 0
Result:
(10 + 20 + 1 + 0 + 0) / 5 = 6.2
AVERAGE vs AVERAGEA
| Function | Treatment of Text | Treatment of TRUE / FALSE |
|---|---|---|
| AVERAGE | Ignored | Ignored |
| AVERAGEA | Counts as 0 | TRUE = 1, FALSE = 0 |
Tip: If your data may contain logical or text values that should affect the result, always choose AVERAGEA.
AVERAGE vs AVERAGEA – Key Differences
| Feature | AVERAGE | AVERAGEA |
|---|---|---|
| Numbers | Included | Included |
| Text Values | Ignored | Counted as 0 |
| TRUE | Ignored | Counted as 1 |
| FALSE | Ignored | Counted as 0 |
| Blank Cells | Ignored | Ignored |
Practical Use Cases
- Survey Responses: TRUE/FALSE answers included in averages
- System Logs: Status values mixed with numbers
- Scorecards: Partial or missing scores counted consistently
- Performance Metrics: Binary indicators averaged with numeric data
Example: Averaging Survey Responses
| Response |
|---|
| TRUE |
| TRUE |
| FALSE |
| Text |
=AVERAGEA(A2:A5)Result: 0.5 — meaning 50% positive responses.
Important Notes
- Text is always evaluated as zero
- Blank cells do not affect the calculation
- Logical values must be TRUE or FALSE (not text equivalents)
- AVERAGEA can lower your average if text exists in the dataset
Final Thoughts
The AVERAGEA function is ideal when working with datasets that contain more than just numbers. By including logical and text values, it gives a more realistic picture of overall performance in mixed-data environments.
Use AVERAGEA when data completeness and logical indicators matter in your analysis.