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.

**Read More: **How to Calculate Average of Multiple Columns in Excel

**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

**Read More:** How to Calculate Average in Excel Excluding 0

**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**.

**Read More: **How to Calculate Average Only for Cells with Values in Excel

**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
```

- Run the code by pressing
**F5**or clicking the**Run Sub**button.

- The output result will be the average of multiple ranges in Excel.

**Download Practice Workbook**

## Related Articles

- How to Find Average of Specific Cells in Excel
- How to Average Only Visible Cells in Excel
- How to Find Average with Blank Cells in Excel
- How to Average a Column in Excel
- How to Average Every Nth Row in Excel
- How to Exclude a Cell in Excel AVERAGE Formula
- How to Fix Divide by Zero Error for Average Calculation in Excel
- How to Ignore #N/A Error When Getting Average in Excel
- [Fixed!] AVERAGE Formula Not Working in Excel

**<< Go Back to Calculate Average in Excel | How to Calculate in ExcelÂ | Learn Excel**