Absolute Value

 

The ABS function in Excel returns the absolute value of a number. In other words: the ABS function removes the minus sign (-) from a negative number, making it positive.

1. For example, the ABS function in cell B1 below returns the absolute value of a negative number.

Absolute Value in Excel

The ABS function has no effect on 0 (zero) or positive numbers.

2. The absolute value of 0 is 0.

ABS function

3. The absolute value of a positive number is the same positive number.

Positive Number

When do we need the ABS function in Excel?

4. For example, calculate the forecast error (difference between the actual and the forecast value) for each month.

Forecast Error

Note: if we look at the sum of these errors (zero), this forecast model seems perfect, but it’s not!

5. Simply use the ABS function to calculate the absolute error for each month.

Absolute Error

6. If you don’t want to display the forecast errors on the sheet, use SUMPRODUCT and ABS.

SUMPRODUCT and ABS

Note: visit our page about the SUMPRODUCT function to learn more about this function.

Let’s take a look at one more cool example.

7. Use the ABS function to calculate the absolute value of each difference.

Absolute Value of Each Difference

8. Add the IF function to test if the values are within tolerance.

IF and ABS

Explanation: if the difference is less than or equal to 3, the IF function returns 1, else it returns 0.

9. Add icons.

Icons

10. Change the tolerance.

Change Tolerance

Note: visit our page about icon sets to learn more about this topic. To view this formatting rule, download the Excel file. Next, on the Home tab, in the Styles group, click Conditional Formatting, Manage Rules.