The average is calculated by summing the specified numbers and dividing them by the total values selected. We use averages because it’s beneficial to contrast different quantities of the same category. In **Microsoft Excel**, we can **calculate the average** of multiple ranges. In this article, we will demonstrate how to calculate the average of multiple ranges in excel.

**Download Practice Workbook**

You can download the workbook and practice with them.

**3 Appropriate Methods to Calculate Average of Multiple Ranges in Excel**

It is quite unknown to many of the users that we can compute the average of multiple ranges in excel. But yes we can do that with some **Excel Functions **in our spreadsheet. To calculate the average of multiple ranges, we are going to use the dataset below. The dataset contains a player column and scores of all those players in a particular game.

As we can see there are 3 players in our dataset. And suppose we want to calculate the average of the first player (**P1**) and second player (**P2**) scores from **Game1 Score **and the first player (**P1**) score from **Game2 Score **and also first player (**P1**) and second player (**P2**) scores from **Game3 Score**. So, we want the multiple ranges of cells average.

**1. Use Excel AVERAGE Function to Calculate Average of Multiple Non-Adjacent Ranges Counting Zero**

In Excel, the **AVERAGE function** computes the average of a set of values, set of range. 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 **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 means of the parameters.

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

Let’s add the multiple ranges to the **AVERAGE function** one by one to calculate the average of the selected ranges by just following the steps down.

**STEPS:**

- First, select the cell where we want the average of the multiple ranges. So, we select cell
**D12**. - Second, type the formula below. As 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)`

- Now, press
**Enter**.

- Now, we can see that the result is in the selected cell
**D12**. And the formula will show in the formula bar.

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

**Read More: ****How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)**

**1.2****. Give Range Name to Multiple Ranges**

We can shorten the formula of the **AVERAGE function **in the same dataset. So let’s go through the procedure.

**STEPS:**

- First, select the ranges
**C5:C9**,**D5:D7**, and**E5:E9**by dragging over the ranges, while dragging and selecting the ranges make sure that you are pressing the**Ctrl**key. - After that, give a name to the selected ranges. As we select the scores, we name the multiple ranges,
**Score**.

- Next, choose the cell where we want the average of the multiple ranges to be calculated. As a result, we choose cell
**D12**. - After that, write down the formula below.

`=AVERAGE(Score)`

- Now, press the
**Enter**key. - Finally, the result will show in cell
**D12**. And if we look at the formula bar, the formula will appear.

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

**Read More:** **How to Calculate Average of Multiple Columns in Excel (6 Methods)**

**Similar Readings**

**How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)****Average Attendance Formula in Excel (5 Ways)****Determine Triple Exponential Moving Average in Excel****How to Calculate Percentage above Average in Excel (3 Easy Ways)****Running Average: How to Calculate Using Excel’s Average(…) Function**

**2. Apply Excel Formula to Determine Average of Multiple Non-Adjacent Ranges Except Zero**

To average values in non-contiguous ranges except zero, we can use a formula that is a combination of some excel functions. There is **SUM function**,** INDEX function,** and** FREQUENCY function**, merged together to calculate the average of multiple ranges.

**2.1. Average Ranges One by One in Excel Formula**

We can add multiple ranges towards the combination of **SUM function**,** INDEX function**, and** FREQUENCY function **at once to find the average, by simply following the instructions down.

**STEPS:**

- In the beginning, select cell
**D12**. - Then, write down the formula below.

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

- After that, press the
**Enter**button. - Finally, we can see the result in cell
**D12**. Multiple non-contiguous ranges excluding zero, are averaged using the formulas above.

🔎** How Does the Formula Work?**

**SUM(C5:C9,D5:D7,E5:E9):**The**SUM function**will simply add up the ranges**C5:C9**,**D5:D7**, and**E5:E9**and return 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 Methods)**

**2.2****. Give the Multiple Range a Name**

The combination of the excel functions can be shortened. So, let’s go through the steps down.

**STEPS:**

- Likewise the previous method of section
**1.2**, drag**C5:C9**,**D5:D7**, and**E5:E9**over the ranges. Be careful to keep the**Ctrl**key pressed while dragging and choosing the ranges. - After that, give the selected ranges a name. We name the numerous ranges
**Scores**as we select the scores.

- Then, select the cell where the average of the several ranges will be calculated. As a result, we select
**D12**. - After selecting the cell, type the following formula.

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

- Finally, press
**Enter**.

**Read More: ****How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)**

**3. Excel VBA to Calculate Average of Multiple Ranges**

We can use **VBA Macros **to calculate the average of multiple ranges. To do this, we are going to use the dataset below, which contains some players and their scores of the games. We want the average of the scores of those games they played under the **Average Score**. Let’s look at the steps below.

**STEPS:**

- Firstly, go to the
**Developer**tab on the ribbon. - Secondly, click on
**Visual Basic**or press**Alt + F11**to open the**Visual Basic Editor**.

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

- Now, write down the
**VBA Code**to calculate the average of multiple ranges. Excel**VBA**has a built-in function,**Average**. 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
```

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

- And by using this
**VBA**code we will get the average of multiple ranges in excel.

**Read More: ****Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)**

**Conclusion**

The above methods assist you to average multiple ranges in excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the **ExcelDemy.com** blog!

## Related Articles

**How to Calculate Average Rating in Excel (7 Methods)****Get Average Time in Excel (3 Examples)****How to Calculate 7 Day Moving Average in Excel (4 Ways)****Calculate Average of Text in Excel (2 Ways)****How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)****[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)****How to Calculate Average and Standard Deviation in Excel**