Excel ABS FunctionMicrosoft Excel is all about managing data, especially numbers. We know that the primary thing with numbers is that they can either be positive or negative. But various data should be essentially positive. Excel has provided an inbuilt function to cater to these requirements, i.e., ABS Function. This tutorial will cover the definition of Excel ABS Function, its syntax, parameters, return type, steps to implement ABS function in your worksheet, and many more! What is ABS Function?The ABS or Absolute function in Excel returns the absolute value of the given number. In simple terms, we can say that the ABS function eliminates the minus symbol () from the specified number, therefore making it a positive number. The absolute value of any given number is defined as the distance of that number from 0 on a number line, nevertheless of its direction (positive or negative). In mathematics, the absolute value of a number 'a' is represented as a unlike in the given below examples: 4 = 4 Though Microsoft Excel does not directly provide any absolute value symbol, it has introduced a special function for getting an absolute value known as the ABS function. While using the ABS or Absolute Function, we will get one of the below given visual and outputs:
Note. Excel absolute value should not be mistaken with Excel absolute cell reference. Both are different concepts. The absolute function eliminates the minus symbol () from the specified number whereas an absolute reference is a special form that remains unaffected while copying the same formula to other cells.SyntaxParameterNumber (required): This parameter represents the number for which you want to fetch the absolute value. ReturnThe Excel ABS function returns the absolute value for the specified number. If the specified number is nonnumeric, this function returns a #VALUE! error. ExamplesExample 1: Using Excel ABS function convert the negative number to positive number.In cases where you require to convert negative numbers to positive numbers quickly, the Excel ABS function is a handy option. Follow the belowgiven steps to change a negative number to a positive number: Step 1: Insert two helper columns We will add two columns one named with 'Difference' in cell D3 and another named with 'Absolute Difference' in cell E3. Refer to the given below image:
STEP 2: Subtract number1 from number2
STEP 3: Insert the ABS function In the next row, start your function with the equal to (=) sign followed by the abs function. The formula will be as follows: =ABS( STEP 4: Insert the number parameter Refer to the number parameter for which you want to find the absolute number. Since here, we have to find the absolute value of the Difference column. So we will refer to the D4 cell. The formula will be as follows: =ABS(D4) STEP 5: Copy the formula across all the cells
Refer to the given below image: Example 2: Using the Excel ABS function find whether a given value (number or percentage) is within expected tolerance or not.The ABS function is also commonly used to find whether a specified value is within expected tolerance or not. Follow the belowgiven steps to check whether the number is in between the expected tolerance or not: Step 1: Insert the absolute helper column We will add the helper columns named with 'Absolute Tolerance' in cell D3. Refer to the given below image: In the ABS Difference helper column, we will apply the Excel combination of IF and ABS function to check whether the number is in between the expected tolerance. STEP 2: Insert the ABS function
It will look like the below image: STEP 3: Apply the IF function
=IF(ABS(A2B2)<=C2, "Yes", "NO") STEP 4: Formula will return the output The above function will return Yes if tolerance if greater than C2 else it will return Fail. In our case, the output is "Yes". STEP 5: Copy the formula across all the cells
Refer to the given below image: STEP 5: Explanation of the formula Inside the IF formula, both the specified numbers are subtracted. The return output could be positive or negative, depending on the actual value. Therefore we have implemented the ABS function to convert the output to a positive number for all the cases. The fetched positive number is then compared to the allowed tolerance as a logical test inside IF. If the number is less than or equal to the specified tolerance, the function returns "OK". If it doesn't meet the specification, it returns "Fail". Example 3: Calculate the Square root of negative number using the Abs function.We will use the SQRT function to calculate the square root of a negative number. But the SQRT function only works for positive numbers. What if the user wants to find the square of a negative number? Follow the belowgiven steps to calculate the square root of a negative number: Step 1: ADD the helper column We will add the helper column helper with 'square root' in cell C3. In this column, we will use a combination of SQRT and ABS functions to find the square root of a negative number. Refer to the given below image: STEP 2: Implement the SQRT Function
STEP 3: Insert the ABS function
STEP 4: Excel will return the output The above formula will return the square root of the negative number. STEP 5: Copy the formula across all the cells
You will have the following output. How to sum absolute values in ExcelWhile working with Excel sometimes we want to get an absolute sum of all numbers, use one of the following formulas: Array formula: The ABS function in Excel can also be used with the SUMPRODUCT function to count absolute variances that satisfies some particular conditions. Regular formula: For example, Let's suppose we have a dataset our worksheet in B2:C5 cells, using the ABS function we will find the sum absolute values. Formula to obtain the absolute value using the SUM function: =SUM(ABS(A2:B5)) Press the Ctrl+ Shift+ Enter and you will have the following output. Formula to obtain the minimum absolute value: =SUMPRODUCT(ABS(A2:B5)) Press the Ctrl+ Shift+ Enter and you will have the following output. As you can see in the below image, both the sum formulas are returning an absolute value: How to find the maximum/minimum absolute value in ExcelYou can also fetch the minimum and maximum absolute value in Excel using the combination of ABS and other Excel formula. Formula of Maximum absolute value is as follows: Formula of Minimum absolute value is as follows: For example, Let's suppose we have a dataset our worksheet in B2:C5 cells, using the ABS function we will find the Min and MAX absolute value. Formula to obtain the maximum absolute value: =MAX(ABS(A2:B5)) Press the Ctrl+ Shift+ Enter and you will have the following output. Formula to obtain the minimum absolute value: =MIN(ABS(A2:B5)) Press the Ctrl+ Shift+ Enter and you will have the following output. You can take the advantage of the ABS function and process a range of data by nesting it into the array argument of the INDEX function. We can incorporate a combination of MAX function, INDEX function and ABS Function to obtain the maximum absolute output. Below is the given formula: =MAX(INDEX(ABS(B2:C5),0,0)) We can incorporate a combination of MIN function, INDEX function and AB Function to obtain the minimum absolute output. Below is the given formula: =MIN(INDEX(ABS(B2:C5),0,0)) Explanation The above formulas return the minimum and maximum values because of INDEX formula. When we set the argument (row_num and column_num) of INDEX function 0 (or omit them), Excel returns the entire array instead of any individual value.
Next TopicExcel Floor Function
