AVERAGEAfunction calculates the average (arithmetic mean) of the values specified in the input. It differs from the
AVERAGEfunction in that it can handle non-numeric values, treating them as 0. This function is commonly used to calculate the average of a range of cells that may contain both numeric and non-numeric values.
- How to use
- Examples of using
AVERAGEAformula not working?
- Similar formulas to
AVERAGEA formula with the syntax shown below, it has 1 required parameter:
- value1, [value2, ...] (required):
The values to include in the calculation of the average. If a value is not numeric, it is treated as 0.
ExamplesHere are a few example use cases that explain how to use the
AVERAGEAformula in Google Sheets.
Calculate the average of a range that contains both numbers and text
When calculating the average of a range that contains both numeric and non-numeric values, the
AVERAGEA function can include the non-numeric values as 0s in the calculation.
Calculate the average of a range that may contain blank cells
AVERAGEA function can handle blank cells in a range and treat them as 0s in the calculation of the average.
Calculate the average of a range that includes errors
If a range of cells includes an error value, such as #DIV/0!, the
AVERAGEA function treats the error as 0 in the calculation of the average.
AVERAGEAnot working? Here are some common mistakes people make when using the
AVERAGEAGoogle Sheets Formula:
Forgetting to input values
If no values are input, AVERAGEA will return a zero instead of an error
Misunderstanding what AVERAGEA includes
AVERAGEA includes numbers, logical values, and text representations of numbers, but excludes empty cells, Boolean values or text
Not understanding the difference between AVERAGE and AVERAGEA
AVERAGEA includes non-numeric values, while AVERAGE calculates the average only for numeric values
The following functions are similar to
AVERAGEA or are often used with it in a formula:
The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.
AVERAGEIFfunction calculates the average of a range of cells that meet a specified criteria. It is commonly used when working with large datasets to quickly calculate the average of a subset of data. The function takes a range of cells to evaluate (criteria_range), a string or value to compare against (criterion), and an optional range of cells to average (average_range). If the average_range is not specified, the function will use the same range as the criteria_range.
AVERAGEIFSformula calculates the average of a range of cells that meet multiple criteria. It is most commonly used to find the average of a range of values that meet specific conditions. The formula takes one required argument (average_range) and any number of optional argument pairs (criteria_range, criterion).
MEDIANfunction returns the median (middle) value of a set of numbers. It is commonly used to find the middle value in a range of data points. If the number of data points is even, it returns the average of the two middle values. This function can be useful in statistical analysis and data visualization.
The MODE formula in Google Sheets returns the most frequently occurring value in a dataset. It is commonly used in statistical analysis to identify the value that occurs most frequently in a set of data. The formula requires at least one input value, but can accept multiple values to include in the analysis.
You can learn more about the
AVERAGEA Google Sheets function on Google Support.