ABS Excel Function (Absolute)

ABS Function Excel (Table of Contents)

• ABS in Excel
• ABS Formula in Excel
• How to Use ABS Function in Excel

ABS in Excel

The ABS in Excel gives the absolute value of a number. It always returns a positive number. It is a built-in function in Excel and is categorized as a Math/Trig Function.

ABS Formula in Excel

Arguments used in ABS Formula in excel

number – The number of which you want to calculate the absolute value in Excel.

In the Function of ABS, the number can be given as directed, in quotes or as cell references. It can be entered as a part of a ABS formula in Excel. It can also be a mathematical operation giving a number as an output. In the function of ABS, If the supplied number argument is non-numeric, it gives #VALUE! error.

Output

ABS in Excel returns the absolute value of the given number. The absolute value of a number is the number excluding its sign. Thus, the positive numbers remain unchanged.

 

Absolute Value in Excel – Illustration

Suppose you want the absolute value of a number say -10.5. In the function of ABS, the number is also given in cell B4.

You can enter the Absolute Value in Excel =ABS(B4) as shown below:

This will return 10.5.

Alternatively, you can also enter the Absolute Value in Excel =ABS(-10.5) as shown below:

In the function of ABS, this will also return the same value.

In the function of ABS, the number can also be entered in quotes as =ABS(“-10.5”)

In the function of ABS, this will also return the absolute value in excel of -10.5.

Suppose the cell B6 contains 10.5 and you want to see what would be the output of the ABS function in Excel when this is given as an argument.

Upon giving either of the following ABS FORMULA in Excel:

=ABS(B6)

=ABS(10.5)

=ABS(“10.5”)

you will get 10.5 as the absolute value in excel. The positive number remains unaffected.

A. Input: =ABS(B6)

Output:

B. Input: =ABS(10.5)

Output:

C. Input: =ABS(“10.5”)

Output:

How to Use ABS Function in Excel

To find Absolute Value in Excel is very simple and easy to use. Let understand the working of ABS function in Excel by some examples.

ABS in Excel Example #1

Suppose you have a list of values given in B3:B10 and you want the absolute values in excel of these numbers.

For the first cell you can type the ABS Formula in Excel:

=ABS(B3)

and press enter. It will return 5.

Now, you can drag it for the rest of the cells and get their absolute values in excel.

All the number in C3:C10 are absolute numbers.

ABS in Excel Example #2

Suppose you have revenue data for the seven departments for your company, and you want to calculate the variance between the predicted and actual revenue.

For the 1^st one, you use the ABS Formula in Excel:

=(D4-E4)/ABS(E4)

and press enter. It will give 0.1667

You can drag it to the rest of the cells to get the variance for the remaining six departments.

ABS in Excel Example #3

Suppose you have some data in B3:B8 and you want to check which of these numbers are positive and which ones are negative. To do so, you can use the function of ABS to find absolute value in excel.

You can use the ABS Formula in Excel:

=IF(ABS(B3) = B3, “Positive”, “Negative”)

If B3 is a positive number then, ABS(B3) and B3 will be same.

Here, B3 = -168. So, it will return “Negative” for B3. Similarly, you can do for rest of the values.

ABS in Excel Example #4

Suppose you have a list of predicted and actual data of an experiment. Now, you want to compare which of these lie within the range of tolerance of 0.5. The data is given in C3:D10 as shown below.

To check which ones are within the tolerance range, you can use the ABS Formula in Excel:

=IF(ABS(C4-D4) <= 0.5, “Accepted”, “Rejected”)

If the difference between the Actual and Predicted is less than or equal to 0.5, it is accepted else it is rejected.

For the first one, the experiment is rejected as 151.5 – 150.5 = 1 which is greater than 0.5.

Similarly, you can drag it to check for rest of the experiments.

ABS in Excel Example #5

Suppose you have a list of numbers and you want to calculate the closest even number of the given numbers.

You can use the following ABS Formula in Excel:

=IF(ABS(EVEN(B3) – B3) > 1, IF(B3 < 0, EVEN(B3) + 2, EVEN(B3) – 2), EVEN(B3))

If EVEN(B3) is the nearest EVEN number of B3, then ABS(EVEN(B3) – B3) is less than or equal to 1.

If EVEN(B3) is not the nearest EVEN number of B3, then

EVEN(B3) – 2 is the nearest value of B3 if B3 is positive

EVEN(B3) + 2 is the nearest value of B3 if B3 is negative

So, if ABS(EVEN(B3) – B3) > 1, then

If B3 < 0 i.e., if B3 is negative => The nearest even value is EVEN(B3) + 2

If B3 is not negative => The nearest even value is EVEN(B3) – 2

If ABS(EVEN(B3) – B3) ≤ 1, then EVEN(B3) is the nearest even value of B3.

Here, B3 = -4.8.

EVEN(B3) = -4

ABS((-4) – (-4.8)) gives 0.8

ABS(EVEN(B3) – B3) > 1 is FALSE, so it will return EVEN(B3).

ABS in Excel Example #6

Suppose you want to identify the closest value of a list of values to a given value, you can do so using the ABS function in Excel.

The list of values in which you want to search are provided in B3:B9 and the value to lookup is given in cell F3.

You can use the following ABS Formula in Excel:

=INDEX(B3:B9, MATCH(MIN(ABS(F3 – B3:B9)), ABS(F3 – B3:B9), 0))

and press CTRL + SHIFT + ENTER (or COMMAND + SHIFT + ENTER for MAC)

Please note that the syntax is an array formula and simply pressing ENTER will give an error.

Let us see the ABS Formula in excel in detail:

• (F3 – B3:B9) will return an array of values {-31, 82, -66, 27, 141, -336, 58}
• ABS(F3 – B3:B9) will give the absolute values in excel and returns {31, 82, 66, 27, 141, 336, 58}
• MIN(ABS(F3 – B3:B9)) will return the minimum value in the array {31, 82, 66, 27, 141, 336, 58} i.e., 27.
• MATCH(27, ABS(F3 – B3:B9), 0)) will look the position of “27” in {31, 82, 66, 27, 141, 336, 58} and return 4.
• INDEX(B3:B9, 4) will give the value of the 4^th element in B3:B9.

This will return the closest value from the provided list of values B3:B9 i.e. 223

You may notice that the curly braces have been automatically added to the entered ABS Formula. This happens when you enter an array formula.

Things to Remember About ABS Function in Excel

• The ABS function in Excel returns the absolute value in excel (modulus) of a number.
• The function of ABS converts negative numbers to positive numbers
• In the function of ABS, positive numbers are unaffected.
• In the function of ABS, #VALUE! error occurs if the supplied argument is non-numeric.

You can download this ABS Function in Excel template here – ABS Function Excel Template

Recommended Articles

This has been a guide to ABS Function in Excel. Here we discuss the ABS Formula in excel and how to use ABS Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• Excel COUNTIF Function with Multiple Criteria
• How to use VALUE Function in excel?
• SING in Function
• VLOOKUP Function
• COUNTIF Formula
• COUNT Formula
• AVERAGE Function in Excel
• EXACT Function in Excel
• LN Excel Function