Statistical
AVERAGEA function: average ranges where TRUE = 1 and text = 0
Understand exactly how AVERAGEA evaluates logical checkboxes and text entries compared with standard AVERAGE.
When text and TRUE/FALSE flags should count in the denominator
When reading a cell range, standard AVERAGE skips text and booleans. AVERAGEA(value1, [value2], ...) includes every non-empty cell in the range: numbers keep their value, TRUE evaluates as 1, FALSE evaluates as 0, and any text string (like "Absent") evaluates as 0.
True empty cells are still ignored by both AVERAGE and AVERAGEA.
| Student (A) | QuizEntry (B) | AVERAGE Treats As | AVERAGEA Treats As |
|---|---|---|---|
| Nora | 10 | 10 | 10 |
| Owen | TRUE (bonus credit) | Ignored | 1 |
| Pia | Absent | Ignored | 0 |
| Quinn | 5 | 5 | 5 |
Side-by-side: AVERAGEA = 4 vs AVERAGE = 7.5
Across B2:B5, AVERAGEA computes (10 + 1 + 0 + 5) / 4 = 4, whereas AVERAGE computes (10 + 5) / 2 = 7.5.
=AVERAGEA(B2:B5)All-blank range returns #DIV/0!
If every referenced cell is truly empty, the count is 0 and AVERAGEA returns #DIV/0! (whereas a range containing text returns 0).
=AVERAGEA(D2:D5)Try the example
Use the original values or change them to test your understanding.