How to Calculate Absolute Difference between Two Numbers in Excel

We have Permissible Speed Limits (mph) for different states along with the tendency of drivers’ Average Speeds (mph). From these two numbers, we want the absolute difference.

Absolute Value and Absolute Difference

Absolute Value

Absolute Value is the interval of a number from zero (0) to that number on the number spectrum irrespective of direction.

Absolute Difference

The Absolute Difference is the absolute value difference between any two given numbers. If the numbers happen to be x and y, the absolute difference is |x-y|.

Taking x = 3 and y = 6 will result in |3-6| = 3, not -3.

How to Calculate the Absolute Difference between Two Numbers in Excel: 4 Ways

Method 1 – Using the ABS Function

Steps:

Use the following formula to calculate the normal difference between B5 and C5.

=B5-C5

Hit Enter and drag down the Fill Handle to get the differences for other entries.

Use the following formula a cell to get the absolute difference between B5 and C5.

=ABS(B5-C5)

Press Enter and drag down the Fill Handle to display the absolute differences of other numbers.

Read More: How to Get Absolute Value in Excel

Method 2 – Using the IF Formula

Steps:

Use the following formula:

=IF(C5>=B5,C5-B5,B5-C5)

Hit Enter and drag the fill handle down to fill the column.

Similar Readings

Method 3 – Using the MAX and MIN Functions

Steps:

Insert the below formula in cell D5.

=MAX(B5,C5) - MIN(B5,C5)

Apply the formula to all the cells.

Method 4 – VBA Custom Function to Calculate the Absolute Difference in Excel

Steps:

Press Alt + F11 or go to the Developer tab and select Visual Basic (in the Code section) to open Microsoft Visual Basic window.
In the window, go to the Insert tab and select Module.

Paste the following Macro code in the Module.

Public Function AbsoluteDifference(Rng1 As Range, Rng2 As Range)
For i = 1 To Rng1.Count
AbsoluteDifference = AbsoluteDifference + Abs(Rng1(i) - Rng2(i))
Next
End Function

In the macro, the macro defines a Custom Function named AbsoluteDifference and Rng1, Rng2 as its Range arguments. Then a VBA FOR loop applies the assigned formula to the Custom Function in each cell within the given range. And we have used the VBA ABS function to calculate the difference between the cells of the selected range.