How to Calculate Average of Multiple Ranges in Excel (3 Methods)

Our sample dataset contains player names and their scores in different games. We’ll use this data to practice calculating the average of specific scores across multiple ranges.

Method 1 – Use the Excel AVERAGE Function to Calculate the Average of Multiple Non-Adjacent Ranges Counting Zero

In Excel, the AVERAGE function computes the average of a set of values, a set of ranges. Sometimes, the numbers are non-adjacent and we have to calculate the values rapidly. Let’s start with a basic understanding of the AVERAGE function in Excel.

➧ Syntax:

The syntax for the AVERAGE function is:

AVERAGE(number1, [number2], …)

➧ Arguments:

number1: [required] The first integer, cell reference, or range for which the average should be calculated.

number2: [optional] Up to 255 more numbers, cell references, or ranges for which the average should be calculated.

➧ Return Value:

The arithmetic mean of the parameters.

1.1. Add the Ranges to the AVERAGE Function One by One

STEPS:

Select the cell where we want the average of the multiple ranges (e.g., D12).
Type the formula below. We want the average of ranges C5:C9, D5:D7, and E5:E9, inside the AVERAGE function. Select all the ranges that we wish to average, by pressing Ctrl and dragging over the ranges.

=AVERAGE(C5:C9,D5:D7,E5:E9)

Press Enter.

The average is output in cell D12. The formula will be shown in the formula bar.

The above result is for non-contiguous ranges, including zero.

1.2. Give Range Name to Multiple Ranges

STEPS:

Select the ranges C5:C9, D5:D7, and E5:E9 while holding the Ctrl key.
Give a name to the selected ranges. E.g., Score.
Choose the cell where you want the average of the multiple ranges to be calculated. E.g., D12.
Type the formula below.

=AVERAGE(Score)

Press the Enter key.
The average will show in cell D12.

The above result is the average of multiple non-contiguous ranges including zero.

Method 2 – Apply Excel Formula to Determine Average of Multiple Non-Adjacent Ranges Except Zero

This method uses a combination of SUM, INDEX, and FREQUENCY functions to calculate the average of multiple ranges excluding zero.

2.1. Average Ranges One by One in Excel Formula

STEPS:

Select cell D12.
Enter the formula below.

=SUM(C5:C9,D5:D7,E5:E9)/INDEX(FREQUENCY((C5:C9,D5:D7,E5:E9),0),2)

Press the Enter.
Cell D12 shows the result.

How Does the Formula Work?

SUM(C5:C9,D5:D7,E5:E9): The SUM function adds up the ranges C5:C9, D5:D7, and E5:E9 and returns the total of the selected multiple ranges.
Output → 788

FREQUENCY((C5:C9,D5:D7,E5:E9),0): The FREQUENCY function returns a vertical array of integers after calculating how often values occur within a range of values. FREQUENCY(C5:C9,D5:D7,E5:E9) becomes FREQUENCY(＄C＄5:＄C＄9,＄D＄5:＄D＄7,＄E＄5:＄E＄9), which locks the reference to a certain cell. Then, FREQUENCY((＄C＄5:＄C＄9,＄D＄5:＄D＄7,＄E＄5:＄E＄9),0) returns a vertical array.
Output → 1

INDEX(FREQUENCY((C5:C9,D5:D7,E5:E9),0),2): The INDEX function returns the value at a certain point in a range or array. It becomes INDEX({1;12},2). That means it returns the result at that location in a range. By omitting zero we have 12 cells.
Output → 12

SUM(C5:C9,D5:D7,E5:E9)/INDEX(FREQUENCY((C5:C9,D5:D7,E5:E9),0),2): This returns the average of multiple ranges. It becomes 788/{12} and returns the average of ranges.
Output → 65.67

2.2. Give the Multiple Range a Name

STEPS:

Similar to section 1.2, select and name the ranges C5:C9, D5:D7, and E5:E9 (e.g., “Scores”).

Select the cell where the average of the several ranges will be calculated. E.g., D12.
Type the following formula.

=SUM(Scores)/INDEX(FREQUENCY((Scores),0),2)

Press Enter.

Method 3 – Excel VBA to Calculate Average of Multiple Ranges

STEPS:

Go to the Developer tab on the ribbon.
Click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.

Another way to open the Visual Basic Editor is to right-click on the sheet and select View Code.

Enter the VBA code to calculate the average of multiple ranges. This code is based on the built-in VBA Average function. With this, we can average as many ranges of cells as we want.

VBA Code:

Sub Average_Multiple_Ranges()
    Dim s As Worksheet
    Set s = Worksheets("VBA")
    s.Range("F5") = Application.WorksheetFunction.Average(s.Range("C:D"))
End Sub