You can use the following formula in Excel to calculate the average difference between two columns:
=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 Excel
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:
=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 3.3.
In other words, this team scores 3.3 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:
=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 – 90 = 10
- Row 2: 104 – 99 = 5
- Row 3: 98 – 102= -4
- Row 4: 105 – 104 = 1
- Row 5: 110 – 112 = -2
- Row 6: 113 – 109 = 4
- Row 7: 121 – 108 = 13
- Row 8: 108 – 100 = 8
- Row 9: 99 – 104 = -5
- Row 10: 93 – 90 = 3
It then uses the AVERAGE function to calculate the average of all these differences:
Average Difference = (10+5-4+1-2+4+13+8-5+3) / 10 = 3.3.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate Average by Date in Excel
How to Calculate Average by Group in Excel
How to Calculate Average and Ignore Zero and Blank Cells in Excel
