WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » ABS Excel Function (Absolute)

ABS Excel Function (Absolute)

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

What Does ABS Function Do in Excel?

ABS Excel function is also known as Absolute function which is used to calculate the absolute values of a given number, the negative numbers given as input are changed to positive numbers and if the argument provided to this function is positive it remains unchanged.

ABS is a built-in function categorized under the Math/Trig function, which gives the absolute value of a number. It always returns a positive number.

Syntax

Excel ABS Function

Arguments used in ABS Formula in excel

  • number – The number of which you want to calculate the absolute value in Excel.
    The number can be given as directed, in quotes, or as cell references. It can be entered as a part of an 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.

How to Use ABS Function in Excel? (with Examples)

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

Example #1

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

Excel ABS Function Example 1

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

=ABS(B3)

ABS Example 1-1

and press enter. It will return to 5.

ABS Example 1-2

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

ABS Example 1-3

All the numbers in C3:C10 are absolute.

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.

Excel ABS Function Example 2

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

=(D4-E4)/ABS(E4)

ABS Example 2-1

and press enter. It will give 0.1667

ABS Example 2-2

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

ABS Example 2-3

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.

Excel ABS Function Example 3

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 the same.

ABS Example 3-1

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

ABS Example 3-2

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.

ABS Example 4

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.

ABS Example 4-1

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

ABS Example 4-2

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

ABS Example 4-3

Example #5

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

ABS Example 5

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))

Excel ABS Function Example 5-1

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).

Excel ABS Function Example 5-2

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.

ABS Example 6

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))

ABS Example 6-1

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 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 4th element in B3:B9.

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

Excel ABS Function Example 6-2

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

  • The ABS function returns the absolute value (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.

Video

Recommended Articles

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

  • Absolute Return
  • Absolute Reference in Excel
  • COUNTIF Formula in Excel
  • COUNTIF Function with Multiple Criteria
  • Excel Worksheet Tab
3 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download ABS Function Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More