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.

**Table of Contents**hide

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

`=B5-C5`

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

`=ABS(B5-C5)`

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.

**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)`

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.

**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)`

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.

`=AbsoluteDifference(B5,C5)`

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.