AVERAGEA function

AVERAGEA returns the average of values in a range, treating text as 0 and logical values as 1 (TRUE) or 0 (FALSE).

=AVERAGEA(value1, ...)

Generate a AVERAGEA formula

Describe what you need. The generator will reach for AVERAGEA where AVERAGEA is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How AVERAGEA reads its arguments
value1requiredAVERAGEA →
ArgumentRequiredDescription
value1RequiredThe first value or range to include in the average. Required. Can contain numbers, text, or logical values.
...RepeatingAdditional values or ranges to include in the average. Optional and repeating. AVERAGEA can accept up to 255 arguments.

Returns

A number representing the arithmetic mean of the input values.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find average actual spending across all months

CategoryMonthBudgetedActualVariance
GroceriesJan500480-20
GroceriesFeb50052020
UtilitiesJan20021010
UtilitiesFeb200195-5
TransportJan300290-10
TransportFeb30031515
=AVERAGEA(480, 520, 210, 195, 290, 315)

Result: 335

AVERAGEA sums the six actual spending values (480+520+210+195+290+315=2010) and divides by 6, yielding 335. This gives the mean spending across all categories and months.

2. Average when data includes a text entry

CategoryMonthBudgetedActualVariance
GroceriesJan500480-20
GroceriesFebAdjusted52020
UtilitiesJan20021010
UtilitiesFeb200195-5
=AVERAGEA(480, "Adjusted", 520, 210)

Result: 302.5

Unlike AVERAGE, AVERAGEA treats the text "Adjusted" as 0 in its calculation: (480+0+520+210)÷4 = 302.5. This demonstrates how AVERAGEA handles mixed data types by coercing text to zero.

3. Find the average budget variance across categories

CategoryMonthBudgetedActualVariance
GroceriesJan500480-20
GroceriesFeb50052020
UtilitiesJan20021010
UtilitiesFeb200195-5
TransportJan300290-10
TransportFeb30031515
=AVERAGEA(-20, 20, 10, -5, -10, 15)

Result: 1.67

AVERAGEA calculates the mean variance: (-20+20+10-5-10+15)÷6 = 10÷6 ≈ 1.67. The positive result shows that actual spending slightly exceeded budget across the household.

Common errors

Which AVERAGEA error are you seeing?
AVERAGEA returned an error#REF!
Verify all referenced cells exist and update the formula to use correct, existing cell addresses.
#VALUE!
Clean your data to remove error values, or wrap references with IFERROR to replace errors with 0 or empty strings.
#NAME?
Correct the spelling to AVERAGEA and re-enter the formula.
ErrorWhy it happensHow to fix it
#REF!A cell reference in your range is invalid, such as pointing to a deleted column or an undefined range.Verify all referenced cells exist and update the formula to use correct, existing cell addresses.
#VALUE!AVERAGEA encounters an error value (such as #DIV/0! or #N/A) within the range being averaged.Clean your data to remove error values, or wrap references with IFERROR to replace errors with 0 or empty strings.
#NAME?The function name is misspelled, such as =AVERGE(...) or =AVERAGAE(...) instead of =AVERAGEA(...).Correct the spelling to AVERAGEA and re-enter the formula.

Tips and when to use something else

  • AVERAGEA treats text as 0 and logical values as 1 (TRUE) or 0 (FALSE)—use AVERAGE if you want to ignore text and logical values entirely.
  • Use AVERAGEIF or AVERAGEIFS when you need to average only cells that meet specific conditions, rather than all cells in a range.
  • AVERAGEA counts all arguments including text and logical values in its denominator, so ensure your data is consistent to avoid unexpected results.
  • For averaging numbers only, AVERAGE is simpler and more commonly used than AVERAGEA.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.AVERAGEIFReturns the average of cells matching a single criterion, useful for conditional averages like mean cost by warehouse or mean inventory per SKU.AVERAGEIFSAVERAGEIFS returns the average of values in a range that meet multiple criteria, enabling filtered averages across complex datasets.COUNTACOUNTA counts the number of non-empty cells in a range, regardless of their content type or whether they contain text, numbers, or errors.COUNTCOUNT counts the number of cells containing numeric values and dates in one or more ranges, ignoring text and blank cells.

Frequently asked questions

When should I use AVERAGEA instead of AVERAGE?
Use AVERAGEA when your data might contain text or logical values that you want to treat as 0 and 1 respectively. AVERAGE ignores text and logical values entirely. For purely numeric data, both functions return the same result.
Does AVERAGEA handle TRUE and FALSE values?
Yes. AVERAGEA treats TRUE as 1 and FALSE as 0 in its calculation. This is useful when averaging Boolean results alongside numbers, such as calculating a rate of successful transactions.
Why does my AVERAGEA formula return a different result than AVERAGE on the same range?
This occurs when your range contains text or logical values. AVERAGEA includes these in its calculation (text as 0, TRUE as 1, FALSE as 0), while AVERAGE ignores them entirely, effectively removing them from the count.
Can AVERAGEA handle mixed numeric and text values in a single range?
Yes. AVERAGEA treats text as 0 and numbers at face value, so a range containing both is valid. However, this behavior can produce misleading averages if text entries are not intentional, so verify your data before interpreting results.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17