Absolute Value in Excel

The ABS function in Excel is designed to calculate the absolute value of a given number. Using Absolute Value in Excel, you can convert negative numbers into positive equivalents, while positive numbers remain unchanged.

To employ the ABS function, you can use the following formula:

Absolute Value = ABS(number)

How to do absolute value in Excel

Here, “number” represents the numeric value for which you want to determine the absolute value.

Let’s delve into a practical example to illustrate the functionality of the absolute value excel:

Consider a set of numbers, both positive and negative, as shown in the screenshot below:

The following are the findings when we use the Excel absolute value function on these numbers:

Positive numbers return their values: for example, 45 is still 45.

Negative numbers on their part are converted to positive values by this function. Consequently, -890, -67, and -74 become 890, 67 and 74 respectively.

Example

Suppose your data is in columns A and B, and you want to find the absolute difference for each corresponding pair of numbers. You can use the ABS function along with SUM like this:

=SUM(ABS(A2:A5 – B2:B5))

Assuming your data starts from row 2 and ends at row 5, this formula will calculate the absolute difference for each pair of numbers in columns A and B, sum them up, and provide the result.

Let’s break down the example:

• A2:A5 represents the range of numbers in Series A.
• B2:B5 represents the range of numbers in Series B.
• A2:A5 – B2:B5 calculates the difference between each corresponding pair of numbers.
• ABS(A2:A5 – B2:B5) gives the absolute value of each difference.
• SUM(ABS(A2:A5 – B2:B5)) summarizes all the absolute differences.

If you have data in different rows or columns, you may need to adjust the cell references accordingly.

Example 1: SUMIF and Absolute Value Excel

Assuming you have the following data:

Formula Used:

=SUMIF(B2:B4,”>=0″,A2:A4)-SUMIF(B2:B4,”<0″,A2:A4)


Explanation:

• SUMIF(B2:B4,”>=0″, A2:A4) calculates the sum of positive numbers in Column A when the corresponding value in Column B is greater than or equal to 0.
• SUMIF(B2:B4,”<0″, A2:A4) calculates the sum of negative numbers in Column A when the corresponding value in Column B is less than 0.
• Subtracting the sum of negative numbers from the sum of positive numbers gives the result.

Result:

(10+25+40)−(−15−6−100)=75+121=196

Therefore, the result using the provided formula is 196, which represents the sum of the absolute differences between corresponding pairs of numbers in Series A and Series B.

Example 2: SUM ARRAY Formula and Excel Absolute Value

Assuming you have the following data:

Array Formula Used:

=SUM(ABS(A2:A6))

Explanation:

• ABS(A2:A6) calculates the absolute values for each cell in the range A2:A6.
• SUM(ABS(A2:A6)) sums up these absolute values.

Result:

Result=ABS(−10)+ABS(25)+ABS(−15)+ABS(20)+ABS(24)

Result=10+25+15+20+24

Result=94

Therefore, the array formula returns the absolute sum of the values in cells A2:A6, which is 94 (not 44 as mentioned in the example). Please double-check the data and formula to ensure accuracy.

Example 3: SUMPRODUCT Formula and Absolute Value in Excel

Assuming you have the following data:

Formula Used:

=SUMPRODUCT(ABS(A2:A4),B2:B4)
Explanation:

• ABS(A2:A4) calculates the absolute values for each cell in the range A2:A4.
• SUMPRODUCT(ABS(A2:A4),B2:B4) multiplies corresponding elements in the two arrays (absolute values of Series A and Series B) and then sums up the results.

Result:

Result=(∣5∣×−3)+(∣2∣×4)+(∣1∣×−2)

Result=(15)+(8)+(2)

Result=25

Therefore, the formula involving ABS ensures that the result is an absolute number, and the final result is obtained by summing the products, which is 25.