The AVERAGEA function in Google Sheets returns the numerical average value in a dataset. It is a useful tool for calculating the average of a set of numbers, including both numerical and non-numerical values.
Parameters
value1: The first value or range to include in the average calculation.value2, ...: [Optional] Additional values or ranges to include in the average calculation.
Step-by-Step Tutorial
-
Using
AVERAGEAwith individual values:- Example:
=AVERAGEA(2, 4, "6", 8) - Result:
5
- Example:
-
Using
AVERAGEAwith a range of cells:- Example: If
A1:A5has the values2, 4, "6", 8, "10", then=AVERAGEA(A1:A5)will return6.
- Example: If
Working with a Single Value, a List, and a Range
AVERAGEA accepts numbers one at a time, as a comma-separated list, or as a range — mix and match freely. Unlike AVERAGE, it also evaluates text as 0 and logical values as 1 (TRUE) or 0 (FALSE) instead of ignoring them.
- Single value:
=AVERAGEA(A1)— returns the value inA1, or0/1if it's text or a logical value. - List of values:
=AVERAGEA(A1, A2, A3)— ifA1,A2,A3are2,4,FALSE,FALSEis treated as0, so this returns2. - Range:
=AVERAGEA(A1:A10)— averages every cell in the range, counting text as0andTRUE/FALSEas1/0. - Mixed:
=AVERAGEA(A1, A3:A6, 10)— you can combine single cells, ranges, and literal numbers in one call.
Use Cases and Scenarios
- Data Analysis: Calculate the average of a dataset that includes both numbers and text values.
- Survey Responses: Find the average rating of a set of responses, even if they include non-numeric values such as "N/A".
- Sales Data: Determine the average revenue generated by different products, including those with missing or incomplete data.
Related Functions
AVERAGE: Find the average of a set of numbers, ignoring non-numeric values.AVERAGEIF: Calculate the average of a range based on specified criteria.AVERAGEIFS: Calculate the average of a range based on multiple criteria.