Code360 powered by Coding Ninjas X Code360 powered by Coding Ninjas X
Table of contents
ABS Function
Need Of ABS Function
Use Of ABS Function
Combination of ABS Function with Other Functions
SUMPRODUCT Formula and ABS Function
Key Takeaways
Last Updated: Mar 27, 2024

ABS Function

Master Python: Predicting weather forecasts
Ashwin Goyal
Product Manager @


People often don't know how to make ABS Functions in Microsoft Excel and get confused. Are you also the one? You are in the right place. 

This blog will not directly go through the theoretical procedure of using ABS Functions, but we will first learn about ABS Functions and their needs. Furthermore, we will discuss using ABS Functions in Microsoft Excel for given data.

You will understand everything about ABS Functions and how to use them after reading this blog. Knowing an ABS Function will help you to know about its applications.

So, don't you want to know how to use ABS Functions in Microsoft excel?

Let's dive into the topic now to know more in detail.

ABS Function

The ABS function in Excel returns a number's absolute value. The ABS function converts a negative integer to a positive one by removing the minus sign (-).

Purpose: Find a number's absolute value.

Return value: A positive number.

Syntax: =ABS (number)


numberThe number for which the absolute value should be calculated.

  Read the Following: abs in C++

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job

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 -


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.


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-


4. How do we calculate ‘Tolerance’?

We can use a formula to determine whether a value is within Tolerance or not:


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:


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!

Previous article
Blank Cell
Next article
Today and Now Function
Live masterclass