How to Calculate Absolute Difference between Two Numbers in Excel

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.

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.

Download Excel Workbook

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

4 Ways to Get Absolute Difference between Two Numbers in Excel

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.

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

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

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.

Read More: Excel formula to find difference between two numbers

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

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.

Read More: Calculate Positive or Negative Difference Between Two Numbers in Excel

Similar Readings

• How to Calculate Significant Difference Between Two Means in Excel
• Excel Pivot Table: Difference between Two Columns (3 Cases)
• How to Calculate Difference Between Two Dates in Days in Excel
• Calculate Time Difference in Numbers (5 Easy Ways)
• How to Find Difference Between Rows and Columns in Excel

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.

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.

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.

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

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.

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

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

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.

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

• How to Find Significant Difference Between Two Numbers in Excel
• Calculate Difference Between Two Times in Excel (8 Methods)
• How to Calculate Time Difference in Minutes in Excel (3 Easy Methods)
• Calculate Difference Between Two Rows in Pivot Table (with Easy Steps)
• How to Calculate Time Difference Between Two Dates in Minutes in Excel
• Pivot Table: Percentage Difference between Two Columns
• How to Calculate Time Difference in Excel VBA (2 Methods)