Excel ABS Function

In Mathematics, there is a term called absolute value which is the non-negative value of a number (x), denoted by |X|. The alphabet X is enclosed in two vertical lines which disregards the initial sign of X and returns a positive value of X regardless of whether X is positive or negative. This implies that either |-x| or |x| would always return a positive X.

The absolute function in excel works exactly as those vertical lines do. Once applied to cells containing numerical values, it returns non-negative value in every case. In this post, we would take you through the workings of the excel ABSOLUTE value function.

ABS Syntax

The syntax for the ABSOLUTE function is:

=ABS(number)

The ABSOLUTE function has just one argument which is the number, and which must be a numerical value, else, it returns a #VALUE error

ABS Examples

With the ABSOLUTE function, negative values are converted to positive, while positive values and zero remain unaffected.

Practical Use of ABS Function

You might be wondering what use the ABS function seeks to serve in the world of financial modeling. Well, the ABS function can calculate the absolute variance between two numbers. For example, in the opex variance analysis of a Finance Business Partner, the actual amount may be lower or higher than the forecast. We can also have the closing GL amount (actual) for the month, resulting in a negative value if reversals are greater than accruals. The goal of the variance analysis is to check the difference between your actual and forecast however we do not want to be bothered about the negative or positive signs. That’s where the ABS function comes in.

So assume we have a forecast value in A2 and actual value in B2, we calculate the variance like this:

When B2 is positive and greater than A2, the result is a positive number, however when A2 is greater than B2, the result is a negative number. Our goal is to calculate absolute variances for all our opex lines, so we use the ABS function to ensure the result is always a positive number.

Sum Absolute Variance

Building on the previous section, we can combine the ABS functions with other functions to improve our variance analysis. For example, the company may decide to show variances in their original sign (positive or negative) but have one section to calculate the total absolute variance.

Using an ordinary sum would take into cognizance the positive or negative sign but combining the ABS function with the SUMPRODUCT function would disregard the signs. Let’s see an example below:

Using a sum function for the absolute variance would result in a value of -200, whilst the SUMPRODUCT & ABS combination would result in the correct absolute variance of 1500

To use the SUMPRODUCT & ABS functions together, use the formula below:

Conditional Count

The SUMPRODUCT & ABS functions can also help count variances based on a defined condition. Using the same example above, let’s assume that we need to count the following:

• Absolute Variances > 50
• Absolute Variances > 150
• Absolute Variance > 200

The formula for achieving our counting needs above is quite similar, we only need to edit the logical test within the formula (>number)

Download Excel File