You can use the following formula in Google Sheets to calculate the average difference between two ranges:

=ARRAYFORMULA(AVERAGE(B2:B11 - C2:C11))

This particular formula calculates the average difference between the values in the range **B2:B11** and **C2:C11**.

The following example shows how to use this formula in practice.

**Example: How to Calculate Average Difference in Google Sheets**

Suppose we have the following dataset in Excel that shows the number of points scored and number of points allowed during various games by some basketball team:

Suppose we would like to calculate the average difference between the values in the **Points Scored** column and the **Points Allowed** column.

We can type the following formula into cell **E2** to do so:

=ARRAYFORMULA(AVERAGE(B2:B11 - C2:C11))

The following screenshot shows how to use this formula in practice:

We can see that the average difference between the values in the **Points Scored** column and the **Points Allowed** column is **4.6**.

In other words, this team scores **4.6 **points more than they allow per game, on average.

**How This Formula Works**

Recall the formula that we used to calculate the average difference between the two columns:

=ARRAYFORMULA(AVERAGE(B2:B11 - C2:C11))

This formula works by first calculating the difference between corresponding values in the ranges **B2:B11** and **C2:C11**.

For example, it calculates:

- Row 1: 100 – 101 =
**-1** - Row 2: 104 – 100 =
**4** - Row 3: 123 – 98 =
**25** - Row 4: 98 – 103 =
**-5** - Row 5: 110 – 109 =
**1** - Row 6: 135 – 120 =
**15** - Row 7: 128 – 114 =
**14** - Row 8: 100 – 100 =
**0** - Row 9: 95 – 104 =
**-9** - Row 10: 101 – 99 =
**2**

It then uses the **AVERAGE** function to calculate the average of all these differences:

Average Difference = (-1+4+25-5+1+15+14+0-9+2) / 10 = **4.6**.

**Note**: Since we used two arrays in our formula, we had to wrap the **ARRAYFORMULA** function around the **AVERAGE** function.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: How to Average Across Multiple Sheets

Google Sheets: Calculate Average and Ignore Zero and Blanks

Google Sheets: How to Calculate Average Excluding Outliers