AVERAGEA Function

Averages values, counting text as 0 and TRUE as 1.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

Want to practice AVERAGEA in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

AVERAGEA works like AVERAGE, but in a range it also counts text (as 0), TRUE (as 1) and FALSE (as 0). Only truly empty cells are skipped.

That makes it handy for TRUE/FALSE columns — the average is the share of TRUEs.

Syntax

=AVERAGEA(value1, [value2], …)

Arguments

ArgumentRequired?What it does
value1YesA value, cell or range.
value2, …NoUp to 255 more values, cells or ranges.

Examples

Example 1 — Share of tasks done

Column B holds TRUE when the onboarding task is complete.

D2fx=AVERAGEA(B2:B6)
ABCD
1TaskDoneShare done
2Laptop set upTRUE0.6
3Email accessTRUE
4Payroll formFALSE
5Safety videoTRUE
6Team lunchFALSE
=AVERAGEA(B2:B6) Explain

Format D2 as a percentage to show 60%.

Example 2 — Text counts as zero

The same scores as on the AVERAGE page: "absent" and "pending" now count as 0. The blank (Di) is still skipped.

D2fx=AVERAGEA(B2:B8)
ABCD
1NameScoreAverage
2Ana8241.67
3Benabsent
4Cy91
5Di
6Ed77
7Fay0
8Guspending
=AVERAGEA(B2:B8) Explain

250 ÷ 6 = 41.67, compared with 62.5 from AVERAGE.

Common mistakes

  • Unexpectedly low results. Labels like "n/a" inside the range drag the average towards zero. Use AVERAGE if text should be ignored.

Notes

  • Typed directly as arguments, text that looks like a number ("5") counts as that number, and other text gives #VALUE!.
  • Related functions: AVERAGE, MAXA, MINA, COUNTA.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.