AVERAGE function

The AVERAGE function in Excel calculates the arithmetic mean of a set of numbers, which is the sum of the numbers divided by the count of those numbers.

Syntax

=AVERAGE(number1, [number2], ...)

• number1, number2, …: These are the numbers or cell references for which you want to calculate the average. You can input up to 255 numbers or ranges.

Description

• The AVERAGE function returns the average (mean) of the numbers provided.
• It can handle both individual numbers and ranges of cells that contain numbers.
• AVERAGE ignores empty cells, text, and logical values, but includes zeros in the calculation.

Example 1: Basic Usage

Given the numbers 5, 10, 15, to calculate their average:

=AVERAGE(5, 10, 15)

Result:

The result is 10, as the arithmetic mean of these numbers is:

$$\frac{5 + 10 + 15}{3} = 10$$

Example 2: Using Cell Ranges

If the numbers 5, 10, 15 are in cells A1:A3, you can use:

=AVERAGE(A1:A3)

Result:

The result is 10, just as in the previous example.

Example 3: Ignoring Empty Cells

If the cells A1:A5 contain the numbers 5, 10, , 20, where A3 is empty, the formula:

=AVERAGE(A1:A5)

Result:

The result is 11.67, because Excel ignores the empty cell and calculates the average as:

$$\frac{5 + 10 + 20}{3} = 11.67$$

Example 4: Including Zeros

If the cells A1:A5 contain the numbers 5, 10, 0, 20, the formula:

=AVERAGE(A1:A5)

Result:

The result is 8.75, because Excel includes the zero in the calculation:

$$\frac{5 + 10 + 0 + 20}{4} = 8.75$$

Usage Notes

• AVERAGE ignores non-numeric values like text, logical values, and errors.
• It counts zeros as valid numbers, so if zeros are present in your dataset and you don’t want them to affect the result, consider using AVERAGEIF or AVERAGEIFS to exclude zeros or specific conditions.
• To find the average of numbers based on a specific condition (e.g., excluding zeros), use AVERAGEIF or AVERAGEIFS.

Practical Applications

• AVERAGE is commonly used to calculate the central tendency of a dataset in financial analysis, statistics, and business scenarios, such as finding the average sales per day or the average temperature over a week.

The AVERAGE function provides a quick and efficient way to calculate the mean of numerical data.