How to Calculate Average Difference in Google Sheets


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

Leave a Reply

Your email address will not be published. Required fields are marked *