EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel ABS Function in Excel
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

ABS Function in Excel

By Jeevan A YJeevan A Y

ABS in Excel

ABS Function (Table of Contents)

  • ABS in Excel
  • How to Use ABS Function in Excel?

ABS in Excel

ABS Function in excel is used to convert any negative number into a positive value. This is one of such function which we can use in any mathematical operation where there are some expectation of getting negative value. The values obtained from the ABS function are the absolute values just by removing the minus (-) sign from the calculated value; for this, we simply need to apply the ABS function in the value or put the formula inside the ABS.

ABS Formula in Excel:

Below is the ABS Formula.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

ABS Formula

The Formula of ABS function is very easy, and that includes only one parameter, i.e. number.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,725 ratings)

Number: This is the required parameter. The number you wish to get the absolute number without its sign.

For this, the function we can give cell reference, we can enter the number directly, and we can enter the number in double-quotes.

How to Use the ABS Function in Excel?

This ABS function is very simple and easy to use. Let us now see how to use the ABS Function with the help of some examples.

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

Example #1

From the list of numbers, convert all the negative numbers to positive numbers using the ABS function.

Example 1-1

Apply the ABS function in excel to get the absolute numbers without its sign.

ABS Example 1-2

The Result will be:

ABS Example 1-3

If you look at the positive numbers, it returns the same value. There are no signs of positive numbers, so it returns the number as it is.

Example #2

From the above example, we have learned how to convert negative to positive numbers. Now take the same example, but the thing is if the number is negative, convert it to positive and if it is not negative, show it as a positive number.

This is done by using the IF condition. IF condition is checking whether the given number is less than zero or not (If (A<0,). If the number is less than zero

Example 2-1

Then it converts the negative number to a positive number by using the ABS function (ABS (A2))

ABS Example 2-2

And if the number is not negative.

ABS Example 2-3

Then the result will be shown as a “Positive Number”.

ABS Example 2-4

Example #3

From the below table, calculate the target vs actual report for the sales team. You are provided with the target and actual achieved number. You need to calculate variance and calculate the variance percentage.

Example 3-1

In order to calculate the variance, we can use the formula as = Actual – Target.

ABS Example 3-2

This would give us the variance value.

ABS Example 3-3

In order to get the variance % value, we use the formula =Variance / Target *100.

ABS Example 3-4

This will be given us a variance % value.

ABS Example 3-5

The problem with this generic formula is that we always get negative numbers if the actual value is less than the targeted value, affecting our variance percentage. Therefore, to eliminate this negative numbers, we can use the ABS function to get the absolute values.

ABS Example 3-6

I just applied the ABS function before I do the calculation of Actual – Target. The result is converted to the absolute value by ABS.

ABS Example 3-7

One more interesting thing here is as soon as we get a positive variance value, variance percentage automatically converted to positive percentages.

Example #4

Without using ABS, we can convert it into a positive number also. These are smart tricks of using excel.

  • Convert negative numbers by multiplying with -1.
  • Use Paste special as an option.

Convert Negative Numbers by Multiplying with -1.

The basic of mathematics is lies in understanding the signs multiplication and those sings multiplication are as follows.

Example 4-1

Since we are trying to convert negative numbers to positive, we can use the last method, i.e. Minus * Minus = Plus

By using the above method, convert the below numbers to absolute numbers.

ABS Example 4-2

Multiply all these numbers with -1 to get the positive values.

ABS Example 4-3

The Result will be :

ABS Example 4-4

Use Paste Special to Convert Negative Numbers to Positive Numbers.

Enter -1 in one cell and follow the below steps.

Convert Negative Numbers to Positive Numbers

Step 1: Copy the value -1, which we have entered in the cell D3.

Copy the value -1

Step 2: Now select all the negative values.

ABS Example 4-7

Step 3: Open paste special dialogue box using ALT + E + S and select Multiply option.

Select Multiply option Example 4-8

Step 4: Now press ok. All the negative values converted to positive values in the same range, and the result will be the same as the below image.

 ABS Example 4-9

Things to Remember

  • ABS can accept only numeric values. Anything other than the numeric value, the error will be #VALUE!
  • No changes will happen to positive numbers. Only negative values are converted to positive values.
  • VBA code to ABS function is WorksheetFucntion.ABS (-650).

Sub ABS_Example

Dim MyValue as Double

MyValue = Application.WorkSheetFunction.ABS(-650)

Msgbox MyValue

End Sub

Recommended Articles

This has been a guide to ABS Function. Here we discuss the ABS Formula and how to use ABS Function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. MID Function in Excel
  2. POWER Function in Excel
  3. EVEN Excel Function
  4. FLOOR Excel Function
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download ABS Function in Excel Template

EDUCBA

Download ABS Function in Excel Template

EDUCBA

डाउनलोड ABS Function in Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more