Excel functions / Statistics
AVERAGEA function
The average, counting text in a range as 0 and TRUE as 1.
Syntax
=AVERAGEA(value1, [value2], …)
Arguments
value: Values, cells or ranges.
When to use it
AVERAGEA averages a range counting text and FALSE as 0 and TRUE as 1, where AVERAGE skips them. It suits a column where "absent" or "no" really means zero.
Examples
Absent counts as zero
A B C 1 Score 2 80 50 3 absent 4 70 In C2:
=AVERAGEA(A2:A4)→ 50The share of TRUE answers
A B C 1 Attended 2 TRUE 75% 3 FALSE 4 TRUE 5 TRUE In C2:
=AVERAGEA(A2:A5)→ 75%
Common mistakes
- Blank cells. Empty cells are still skipped, even by AVERAGEA. Type 0 where a zero is meant.
AVERAGEA in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: AVERAGEA
- Danish: MIDDELV
- Dutch: GEMIDDELDEA
- Finnish: KESKIARVOA
- French: AVERAGEA
- German: MITTELWERTA
- Hungarian: ÁTLAGA
- Italian: MEDIA.VALORI
- Norwegian: GJENNOMSNITTA
- Polish: ŚREDNIA.A
- Portuguese (Brazil): MÉDIAA
- Portuguese (Portugal): MÉDIAA
- Russian: СРЗНАЧА
- Spanish: PROMEDIOA
- Swedish: AVERAGEA
- Turkish: ORTALAMAA
Try AVERAGEA in the playground