Average Data in Multiple Non-Contiguous Ranges in Excel

The AVERAGE function determines the average value of the numbers that are passed to it in the argument list. Excel first totals all of the numerical values, then divides that total by the number of numerical values to arrive at the average.

The AVERAGE function accepts several arguments, up to a maximum of 255 in total, in the pattern num1, num2, num3, and so on. Arguments can be anything from constants and arrays to ranges and cell references. They can even be numbers. There is no processing done on cells that are either empty or contain text or logical values. On the other hand, values of zero are included.

With the AVERAGEIFS function, it is possible to ignore values that are zero. The AVERAGE function will disregard logical values as well as numerical values that are entered in text format.

When you want to determine what the average value is for a certain range in Excel, you can use the function called AVERAGE. But occasionally the numbers are in non-contiguous ranges, which needs to be calculated. In this scenario, the question is how you can easily average these values in various ranges in Excel, including or excluding zero as appropriate.

In this tutorial, learn how you can easily calculate the average of numbers in non-contiguous cells including zero and excluding zero.

Average in Non-Contiguous Ranges Including Zero

Let's start with the step-by-step process that you should follow to calculate the average of numbers in non-contiguous cells including zero.

Step 1

In our example, we have the number of items produced in every month from January to August. See the following screenshot.

We want to calculate the average number of production of items from January to August including 0.

Step 2

There are 4 items namely, Item1, Item2, Item3 and Item4 in our Excel. To calculate the average of the number, select one blank cell and use the below given formula. In our example we have selected the cell F2.

=AVERAGE(B2:C9,D2:E9)

Here in our example, we have calculated from the ranges B2:C9, D2:E9. You can use the cell ranges according to your need.

After adding the formula, press enter. You can see the calculated average which includes 0.

Average in Non-Contiguous Ranges Excluding Zero

Now, let's see how you can calculate the average of numbers in non-contiguous cells excluding zero.

Considering the above example, if you want to calculate the average of the values that are not consecutive and do not include 0, you can use the following formula.

=SUM(B2:C9,D2:E9)/INDEX(FREQUENCY((B2:C9,D2:E9),0),2)

In our case, we have used from the ranges B2:C9, D2:E9. You can change the cell ranges according to your need.

In the above example, the frequency and Index function is used because, the FREQUENCY function will return a frequency distribution, which is a list that demonstrates the values' occurrences at various intervals. The INDEX function finds and returns the value that corresponds to a specific position inside a range or array.

Entering the above formula will produce the average excluding 0 in the range of cells. See the following screenshot.