📎 This article includes 1 downloadable practice file ↓
In this article
AVERAGEA treats text as 0 and TRUE as 1. Use it when “absent” should drag the average down rather than be ignored.
Syntax
=AVERAGEA(value1, [value2], ...)| Argument | What it means |
|---|---|
value1… |
Values or ranges. |
Examples
Example 1
=AVERAGEA(C2:C31)Average score where “AB” (absent) counts as zero.
AVERAGEA in practice
Where you will use it
- Average a column where 'Absent' should count as zero
- Score sheets with TRUE/FALSE ticks
- Attendance where text entries mean no value
Worked example
Running average where the text 'Absent' counts as 0.
| Student | Score | Formula | Result |
|---|---|---|---|
| Aarav | 80 | =AVERAGEA($B$2:B2) | 80 |
| Kabir | Absent | =AVERAGEA($B$2:B3) | 40 |
| Meera | 70 | =AVERAGEA($B$2:B4) | 50 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Expecting it to match AVERAGE | AVERAGEA counts text as 0, so 'Absent' pulls the average down; AVERAGE skips it |
| Blank cells | Blanks are still ignored by AVERAGEA; only text and logicals count |
Related functions
AVERAGE · MAXA
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Statistics advanced33 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 36 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong