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)

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.

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