How to Calculate Absolute Difference between Two Numbers in Excel

Get FREE Advanced Excel Exercises with Solutions!

Using absolute values to differentiate incidents such as loss, gain, due, etc. is pretty common among users. Excel absolute difference between two numbers is used in worksheets when the mere difference is asked or needed. Excel ABS function returns the absolute value of any given difference or value. We can use other formulas such as IF, MAX and MIN as well as a VBA Custom Function (i.e., AbsoluteDifference) to get the absolute difference between numbers.

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

Dataset-Excel Absolute Difference Between Two Numbers

In this article, we use ABS, IF, MAX, and MIN functions as well as VBA Custom Function to find Excel absolute difference between two numbers.


The Idea of 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. Get -4 or 4 on the number line, the absolute value of -4 or 4 will be 4. The interval between zero (0) to -4 or 4 is 4 therefore both of the numbers have the absolute value of 4.

Absolute Value


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|.

Absolute Difference

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


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

The ABS function results in absolute value and we can use it almost everywhere. Other alternatives such as the IF formula with a logical_test and VBA Custom Function also come in handy. Follow the latter section to get the absolute difference between two numbers.


Method 1: Using ABS Function to Calculate Absolute Difference between Two Numbers

The ABS function takes numbers as its argument. We can simply calculate the difference between numbers using Arithmetic Operator (i.e., Subtraction) and then use the ABS function to result in the absolute difference.

Step 1: Use the following formula in any adjacent cell (i.e., D5) to calculate the normal difference.

=B5-C5

ABS Function-Excel Absolute Difference Between Two Numbers

Step 2: Hit ENTER then Drag the Fill Handle to get the differences for other entries.

Normal difference

You see Excel shows Negative(–) signs before the differences as there is a larger and smaller number issue. You can overcome these by using the ABS function before number differences.

Step 3: Write the below formula in any blank cell (i.e., E5).

=ABS(B5-C5)

ABS Function

The ABS function takes the difference B5-C5 as its number argument and returns the absolute difference.

Step 4: Press ENTER afterward Drag the Fill Handle to display the absolute differences of other numbers.

Absolute Difference-Excel Absolute Difference Between Two Numbers

Read More: How to Get Absolute Value in Excel


Method 2: Absolute Difference Calculation Using IF Formula

Alternatively, the IF function can perform the absolute difference calculation. Firstly, the IF formula determines the larger value using its logical_test and then performs Subtraction.

Step 1: Type the following formula in any blank cell (i.e., D5).

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

IF Function-Excel Absolute Difference Between Two Numbers

The formula performs C5>=B5 (logical_test) and afterward executes C5-B5 if the logical_test returns TRUE (the content of C5 is greater or equal to the content of B5) otherwise B5-C5.

Step 2: To execute the formula use the ENTER key. Drag the Fill Handle to apply the formula to other cells.

IF Function Outcome


Similar Readings


Method 3: Calculating Absolute Difference Using MAX and MIN Functions

Other things that we can do to declare the large and small numbers among two numbers is to use the MAX and MIN functions. Then a simple Subtraction results in the absolute difference.

Step 1: Insert the below formula in cell D5.

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

MAX MIN Function-Excel Absolute Difference Between Two Numbers

The MAX and MIN functions take numbers and return the Maximum and the Minimum number of them respectively. Providing both numbers in those functions allows you to calculate the absolute difference between those two numbers.

Step 2: Apply the formula to all the cells and Excel displays the absolute difference between them.

Outcome


Method 4: VBA Custom Function to Calculate Absolute Difference in Excel

Normally there is no direct Excel function that calculates or finds the absolute difference between two numbers. However, we can write a couple of lines of VBA Macro code to generate a Custom Function to calculate the absolute difference.

Step 1: Use ALT+F11 or go to the Developer tab > Visual Basic (in the Code section) to open Microsoft Visual Basic window. In the window, go to the Insert tab > Select Module.

VBA-Excel Absolute Difference Between Two Numbers

Step 2: 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

Macro

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.

Step 3: Now, back to the Worksheet, try typing AbsoluteDifference in cell D5. Excel displays the newly created Custom Function, Click on it.

Custom formula

Step 4: Provide necessary cell references to the function as depicted in the image below.

=AbsoluteDifference(B5,C5)

The Custom Function takes two arguments as Range (i.e., B5, C5).

Formula Insertion

Step 5: To execute the function Hit ENTER then Drag the Fill Handle. You see all the absolute differences get displayed similar to the below depiction.

Outcome


Download Excel Workbook


Conclusion

In this article, we use typical functions as well as VBA custom-made functions in Excel to calculate the absolute difference between two numbers. These above-mentioned formulas work pretty well when it comes to returning the absolute difference. Hope you find these formulas fulfilling your purposes. Comment, if you have further inquiries or have anything to add.


Related Articles


<< Go Back to Excel ABS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo