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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Calculate Average of Multiple Ranges in Excel: 3 Methods

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. 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 the first player (P1) and second player (P2) scores from Game3 Score. So, we want the multiple ranges of cells average.

3 Appropriate Methods to Calculate Average of Multiple Ranges in Excel


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], …)

3 Appropriate Methods to Calculate Average of Multiple Ranges in Excel

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.
  • Then, 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)
  • Now, press Enter.

3 Appropriate Methods to Calculate Average of Multiple Ranges in Excel

  • Now, we can see that the result is in the selected 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

We can shorten the formula of the AVERAGE function in the same dataset.

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. We selected cell D12.
  • After that, write down the formula below.
=AVERAGE(Score)
  • Now, press the Enter key.
  • Finally, the result will be shown in cell D12. And if we look at the formula bar, the formula will appear.

3 Appropriate Methods to Calculate Average of Multiple Ranges in Excel

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


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 are SUM, INDEX, and FREQUENCY functions 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 the SUM, INDEX, and FREQUENCY functions at once to find the average, by simply following the instructions.

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)

3 Appropriate Methods to Calculate Average of Multiple Ranges in Excel

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

3 Appropriate Methods to Calculate Average of Multiple Ranges in Excel

🔎 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(C5:C9,D5:D7,E5:E9), which locks the reference to a certain cell. Then, FREQUENCY((C5:C9,D5:D7,E5:E9),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

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

STEPS:

  • Likewise, in the previous method of section 1.2, drag ranges C5:C9, D5:D7, and E5:E9. Be careful to keep the Ctrl key pressed while dragging and choosing the ranges.
  • After that, give the selected ranges a name. We named the ranges as Scores.

3 Appropriate Methods to Calculate Average of Multiple Ranges in Excel

  • 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 Calculate Average Only for Cells with Values in Excel


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.

STEPS:

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

Excel VBA to Calculate Average of Multiple Ranges

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

  • Now, write down 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
  • Finally, run the code by pressing F5 or clicking the Run Sub button.

Excel VBA to Calculate Average of Multiple Ranges

  • And by using this VBA code we will get the average of multiple ranges in Excel.

Excel VBA to Calculate Average of Multiple Ranges


Download Practice Workbook

You can download the workbook and practice with them.


Conclusion

The above methods assist you in calculating the average of 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. I’ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo