AVERAGEA

AVERAGEA computes the arithmetic mean of a range treating text as 0 and TRUE as 1, FALSE as 0. Unlike AVERAGE, it does not ignore text or logical values, so it produces a lower mean when non-numeric entries are present.

Excel

=AVERAGEA(B2:B5)

Google Sheets

=AVERAGEA(B2:B5)

✓

Verified example

Computed by a real spreadsheet engine on the sample data below.

Name	Score
Alice	85
Bob	90
Carol	absent
Dave	78

=AVERAGEA(B2:B5)→63.25

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Cell row 1 column 1	Cell row 1 column 2
Cell row 2 column 1	Cell row 2 column 2
Cell row 3 column 1	Cell row 3 column 2
Cell row 4 column 1	Cell row 4 column 2
Cell row 5 column 1	Cell row 5 column 2

Formula

Runs server-side · free · no signup

How it works

1Select the range including any text or logical values: =AVERAGEA(B2:B5).
2AVERAGEA counts every cell — text becomes 0, TRUE becomes 1, FALSE becomes 0.
3The result is the sum divided by the total count including non-numeric cells.

Need a version for your data?

Try: “Average scores in a column where some cells contain the word 'absent' instead of a number”

COUNTA COUNTBLANK MODE

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026

AVERAGEA

Verified example

Try it with your data

How it works

Related