You are reading the article Absolute Function In Excel (Abs) updated in September 2023 on the website Cersearch.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Absolute Function In Excel (Abs)
ABSOLUTE Function in Excel (ABS)Returns the absolute value of a number
Written by
CFI Team
Published July 31, 2023
Updated July 7, 2023
What is the ABSOLUTE Function in Excel (ABS)?The ABS Function[1] in Excel returns the absolute value of a number. The function converts negative numbers to positive numbers while positive numbers remain unaffected.
FormulaABSOLUTE Value = ABS(number)
Where number is the numeric value for which we need to calculate the Absolute value.
How to use the ABSOLUTE Function in Excel?Let’s take a series of numbers to understand how this function can be used.
In the screenshot above, we are given a series of numbers. When we use the ABSOLUTE function, we get the following results:
For positive numbers, we get the same result. So 45 is returned as 45.
For negative numbers, the function returns absolute numbers. So for -890, -67, -74, we got 890, 67, 74.
To learn more, launch our free Excel crash course now!
Examples of the ABSOLUTE Function in ExcelFor our analysis, we want the difference between Series A and Series B as given below. Ideally, if you subtract Series A from Series B you might get negative numbers depending on the values. However, if you want absolute numbers in this scenario, we can use the ABSOLUTE function.
Let’s see a few examples of how ABS can be used with other Excel Functions.
1. SUMIF and ABSWe all are aware that SUMIF would sum up values if certain criteria within the range given are met. Let’s assume we’ve been given a few numbers in Column A and Column B as below:
Now, I wish to subtract all negative numbers in Column B from all positive numbers of Column A. I want the result to be an absolute number. So I can use the ABS function along with SUMIF in the following manner:
The result is 79. Excel added 15 and 6 from Column A and subtracted 100 from Column B to give us 79, as we used ABS function instead of -79.
2. SUM ARRAY Formula and ABS FunctionThe Excel array formulas help us to do multiple calculations for a given array or column of values. We can use SUM ARRAY along with ABS to get the absolute value of a series of numbers in column or row. Suppose we are given a few numbers as below, so in this scenario, the SUM array formula for absolute values would be =SUM(ABS(A2:A6)).
As seen in the screenshot above, the array formula also returned the value 44 in cell A7, which is the absolute value of the data entered in cells A2:A6.
3. SUMPRODUCT Formula and ABS FunctionThe SUMPRODUCT function allows us to include the ABS function to provide absolute numbers. Suppose we are given the following data. If we would just use the SUMPRODUCT formula, we would get a negative number as shown below:
However, using ABS Function, we can get the absolute number as a result. The formula to be used would be:
To learn more, launch our free Excel crash course now!
ABS as a VBA FunctionIf we wish to use the ABSOLUTE function in Excel VBA code, it can be used in the following manner. Let’s assume I need ABS of -600 so the code would be:
Dim LNumber As Double
LNumber = ABS(-600)
Now in the above code, the variable known as LNumber would now contain the value of 600.
Free Excel CourseIf you want to learn more Excel functions with your own online instructor, check out CFI’s Free Excel Crash Course! You’ll get step by step tutorials and demonstrations on how to become an Excel power user.
Additional ResourcesThanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
You're reading Absolute Function In Excel (Abs)
Update the detailed information about Absolute Function In Excel (Abs) on the Cersearch.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!