Need Of ABS Function
For calculating forecast or absolute error, we need the ABS function. The ABS function returns a number's absolute value. Absolute value can be thought of as a number's distance from zero on a number line. Negative values are converted to positive numbers using ABS. Positive and zero (0) numbers are unaffected.
The ABS function only accepts a single input, which must be a number. ABS produces a #VALUE! Error if the number is not numeric.
Now we will see a basic example to undersantand ABS -
=ABS(-6) // returns 6
=ABS(2) // returns 2
=ABS(0) // returns 0
Use Of ABS Function
This section will see how we can use the ABS function with examples.
Let's look at a series to see how this function can be applied.
1. Take the following data.
2. In the B1 cell, write formula =ABS(A1).
3. Now, take the cursor to the corner of the cell and drag it to the B5 cell.
It will show ABS values of data from A1 to A5.
Combination of ABS Function with Other Functions
ABS can be used in Excel spreadsheets in conjunction with other functions such as SUM, MAX, MIN, AVERAGE, and others to compute the absolute value of positive and negative values.
Let's see some of them -
SUMIF and ABS
We're all familiar with SUMIF, which sums numbers if specific criteria inside a given range are met. Let's assume we've been given the following numbers in Columns A and B:
Now if we have to remove all of the negative values in Column A from all of the positive numbers in Column B. We'd like the result to be exact. As a result, we may use the ABS function in combination with SUMIF in the following way:
Result- 21
-56-75+10+44+56 = -21
Now taking ABS of -21 which is 21.
SUMPRODUCT Formula and ABS Function
We may use the ABS function with the SUMPRODUCT function to get absolute numbers. Let's say we're given the following information. We'd receive a negative value if we only used the SUMPRODUCT formula, as illustrated below:
But now, if we want to get the positive value, we can use the ABS function with SUMPRODUCT.
FAQs
1. How can we use the ABSOLUTE function in Excel VBA (Visual Basic for Applications) code?
If we want to use the ABSOLUTE function in Excel VBA code, we can do so in the following way. Assuming we require ABS of -300, the code would be:
Dim LNumber As Double
LNumber = ABS(-300)
Now, LNumber will contain the absolute value of -300, which is 300.
2. What is an Absolute variance?
A common challenge is calculating the variance of two numbers. For example, with a predicted value in A2 and an actual value in B2, we can calculate variance as follows:
=B2-A2 // negative or positive result
Variance is a positive value when B2 is more significant than A2. However, when A2 is more important than B2, the outcome is negative. You can use ABS in this way to assure that an outcome is a positive number:
=ABS(B2-A2) // ensures positive result
3. How can we find the square root of a negative number?
If we pass a negative number in the SQRT function, it will return #NUM! Error.
SQRT(-9) // returns #NUM!
So, for handling it like a positive number, we use ABS function inside SQRT function as follows-
SQRT(ABS(-9))
4. How do we calculate ‘Tolerance’?
We can use a formula to determine whether a value is within Tolerance or not:
=IF(ABS(actual-expected)<=tolerance,"Good","Bad")
5. How do we count absolute variances with conditions?
To count absolute variations that meet specified parameters, use the ABS function with the SUMPRODUCT function. For example, we may use the following formula to count absolute variances of more than 230:
=SUMPRODUCT(--(ABS(variances)>230))
Key Takeaways
In this article we have extensively discussed the topic of ABS Function. Furthermore, we learned how to manipulate data using ABS Function.
We hope that this blog has helped you enhance your knowledge regarding encryption and if you would like to learn more, check out our articles on AND & OR Function in Excel and Pie chart.
Check out this problem - First Missing Positive
Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, Javascript, System Design, etc. Enroll in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.
Do upvote our blog to help other ninjas grow.
Happy Learning!
8+ registered 