Google Sheets AVERAGEA Function: Syntax, Examples & Tips

What Is AVERAGEA?

AVERAGEA calculates a mean like AVERAGE, but it treats non-numeric cells differently. Where AVERAGE skips text and logical values, AVERAGEA counts them: text is treated as 0, TRUE as 1, and FALSE as 0. The result is an average over every non-empty cell in the range, not just the numeric ones.

How AVERAGEA Works

For each referenced cell, AVERAGEA decides what it is worth:

Empty cells are still ignored, just as with AVERAGE. So the key question AVERAGEA answers is: "If every filled-in cell counts, what is the mean?"

Basic Examples

A column with some text entries

Suppose B2:B6 contains 10, 20, absent, 30, and an empty cell.

=AVERAGEA(B2:B6)

Returns 15: the total of 10 + 20 + 0 + 30 = 60, divided by 4 non-empty cells. =AVERAGE(B2:B6) would return 20, because it divides by only the three numbers.

Share of TRUE values in a checkbox column

=AVERAGEA(C2:C101)

If C2:C101 is a column of checkboxes, each ticked box is TRUE (1) and each unticked box is FALSE (0). The result is the proportion of boxes that are ticked, for example 0.42 for 42%.

Mixing numbers and logical values

=AVERAGEA(D2:D20)

Useful for scoring sheets where some answers are entered as TRUE/FALSE and others as numeric points.

AVERAGEA vs. AVERAGE

Choose AVERAGEA when a text entry genuinely represents a zero score. Choose AVERAGE when text means "no data" and should not affect the result.

Common Use Cases

• Attendance or completion rates: a column of checkboxes averaged into a percentage
• Scoring with placeholders: entries such as "missed" should count as zero points
• Quality checks: pass/fail columns stored as TRUE/FALSE
• Survey data: yes/no responses recorded as logical values

Common Errors

• Unexpectedly low results: Header rows or notes inside the range are counted as 0. Make sure the range covers data only.
• Numbers stored as text: A value such as "45" typed as text is counted as 0, not 45. Convert it to a real number first.
• #DIV/0!: Every referenced cell is empty.
• Error values: Any error in the range makes the whole formula return that error.

Tips and Best Practices

• Format the result as a percentage when averaging checkboxes; it reads as a completion rate.
• If you want to count blanks as zero too, fill them with 0 or use =SUM(range)/ROWS(range).
• To compare the two behaviours, place AVERAGE and AVERAGEA side by side. A gap tells you how much text or logical data the range contains.
• For conditional averages, use AVERAGEIF; note that it ignores text in the averaged range, like AVERAGE.

Related Functions

• AVERAGE: ignores text and logical values
• AVERAGEIF: averages with a condition
• COUNT: counts numeric cells only
• IF: convert text to numbers before averaging