Know the Mean with Excel’s AVERAGE Function

Averages are frequently used to express a representative value from a set of numbers, groups, etc. This gives us a rough estimate and it takes into account every value in the set which is very helpful when working with large data. In financial modeling, the AVERAGE function in Excel is useful in calculating a representative value such as average revenue per unit, average revenue per user, average wage among employees, etc.

In this article, we will discuss what “average” is in the AVERAGE function in Excel and learn with examples of how to use Excel’s AVERAGE function.

What’s the Mean-ing in Excel’s AVERAGE Function

Before we dive into how to use Excel’s AVERAGE function, let’s first understand what “average” the function is calculating.

According to Meriam Webster, an average is a single value that summarizes or represents the general significance of a set of values.

This representative single value can be the Median, Mode, and Mean with each describing different values. But the word “average” commonly refers to the Arithmetic Mean.

The Arithmetic Mean is the most common type of average (other types are Geometric and Harmonic Mean) and is calculated by adding all values and dividing the sum by the number of values.

For example, what is the arithmetic mean of the following 5 numbers: 10, 20, 30, 40, and 50.

Using the formula above, adding the numbers 10, 20, 30, 40, and 50 results in a sum of 150. Then divide the sum by the total number of values which is 5, the arithmetic mean is 30.

This concept of Arithmetic Mean is what Excel’s AVERAGE function is based on. The AVERAGE formula in Excel calculates the arithmetic mean of a set of numbers and is under the Statistical Formulas.

The AVERAGE formula in Excel:

Where:

Number1 – Required argument that represents the numeric value, cell reference, or range of cells to be averaged.

[Number2], … Optional arguments that represent the numeric value, cell reference, or range of cells to be averaged.

Let’s use below numbers in Column A and Column B to illustrate how the AVERAGE formula in Excel works:

Keep in mind, the AVERAGE function in Excel works in columns, rows, ranges, tables, and cell references.

Examples of How to Use Excel’s AVERAGE Function

To illustrate how to use Excel’s AVERAGE function, assume US-based clothing company has the following sales across six (6) regions for all twelve (12) months.

Using the AVERAGE function in Excel, we will answer the following questions:

  1. What is the average sales for January?
  2. What is the average sales for the North West region?
  3. What is the average sales for South West and North East combined?

AVERAGE Formula in Excel with One Argument

Question#1 What is average sales for January?

To answer Question #1, we will use cell range B3:B8 which contains all data for January.

The AVERAGE formula in Excel =AVERAGE(B3:B8) has a 2-step process:

  1. Adds all values in cell range B3 to B8 Then
  2. Divide the sum by the 6 regions

Notice the AVERAGE formula in Excel has only one argument, Number1, represented by cell range(B3:B8).

The average sales for January is $979.

You can reference the table when calculating the average sales for the remaining months using the AVERAGE function in Excel.

Question#2 What is the average sales for the North West region?

To answer Question #2, we will use cell range B3:M3 which contains all data for the North West region.

The AVERAGE formula in Excel =AVERAGE(B3:M3) has a 2-step process:

  1. Adds all values in cell range B3 to M3, then
  2. Divide the sum by 12 months

Also, take note the AVERAGE formula in Excel has only one argument, Number1, represented by cell range(B3:M3).

The average sales for the North West region is $1,605.

You can reference the table when calculating the average sales for the other regions using the AVERAGE function in Excel.

AVERAGE Formula in Excel with More Than One Argument

Question#3 What is the average sales for South West and North East combined?

Answering Question #3 will require more than one argument in the AVERAGE formula in Excel. The formula can accommodate 1 to 255 arguments.

The data needed to calculate the average sales for South West and North East are in cell ranges B5:M5 and B8:M8 respectively.

The AVERAGE formula in Excel =AVERAGE(B5:M5,B8:M8) also has a 2-step process:

  1. Adds all values in cell range B3 to M3 and B8 to M8 Then
  2. Divide each of the sums of the two ranges by 12 months

The required argument, Number1, is cell range B5:M5, and the optional argument, [Number2], is cell range B8:M8.

The average sales for the both South West and North East combined is $1,127.

You can reference the table when calculating average sales for whatever possible combination of regions you prefer using the AVERAGE function in Excel.

Key Takeaways on Using Excel’s AVERAGE Function

  • Excel’s AVERAGE function is not limited to calculating averages for numeric values in a table range, row range, column range, and non-adjacent cells. You can use the AVERAGE function for individual cells as well as entering numeric values directly in the arguments.
  • The AVERAGE formula in Excel only works with numeric values such as whole numbers, fractions, decimals, time, date, etc. The formula can accommodate 1 to 255 arguments.
  • The AVERAGE function in Excel does not ignore zero values but ignores blank cells.

Notice how the Average Sales for the North West region is lower when May, June, and July sales are zero. Despite containing zero values, the total sum is still divided by 12 months.

Take note of how the Average Sales for North West regions differ when May, June, and July sales are left blank. Since blank cells are ignored, the total sum is divided by 9 months only.

  • The AVERAGE function in Excel will result in errors if any of the arguments in the AVERAGE formula contain errors as well.

Was this helpful?