Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
The absolute value function (ABS Function) allows an Excel user to return the absolute value of a number. So, if the ABS function is used with a negative number, a positive number would be returned. In the event that the ABS function is used on a positive number, a positive number would also be returned. Additionally, it can be added to other calculations to find the absolute value of part of the calculation or the final result.
ABS Function Syntax for Excel Worksheets
For this function to work in an Excel worksheet, it needs to be used with a real number (or reference a real number). The function can work in any of the three scenarios describe below:
In this case, the absolute value of 5 will be returned. The result that will remain in the cell will be a 5. This shows that when a positive number is used with the ABS functions the same positive number will be returned.
In this case, the absolute value of -5 will be returned. The result that will remain in the cell will be 5. Any negative numbers used in with this function will become positive numbers.
In this case, the absolute value of the number that is located in cell A1 will be returned. Additionally, cell references that have arithmetic (e.g. A1+B2) will also result in a positive number.
In this case, the absolute value of the result will be returned. Any arithmetic calculated with this function will return a positive value. The answer that would be returned for this function would be 25.
Here the absolute value will only provide an absolute value for part of a formula. It can be added to any formula to find the absolute value of any part of a formula. This this situation might be used with some type of interval calculation.
Entering the Formula Into a Worksheet
Whether the function is being use with a reference to another cell or as a number, it needs to be added to a cell in the form of a formula like the examples previously discussed. To create a formula, a cell needs to be clicked and "=ABS(" needs to be typed in that cell or in the formula bar under the ribbon. After the open parenthesis, a cell reference, arithmetic with cell references, a number or arithmetic with numbers needs to be added. This is then followed with a closed parenthesis. After the formula is completed, the enter button can be pressed to execute the formula.
Inserting the ABS Function
The ABS function can also be inserted into a worksheet. To do so, a cell is first selected. Next, the formulas tab can be clicked, followed by the selection of the "Math & Trig" button on the Excel ribbon. The ABS selection from the list is then chosen.
After the function argument window appears, a cell reference, arithmetic with cell references, a number or arithmetic with numbers needs to be added to the number field. Next, Ok can be selected to return the absolute value into the cell that was selected.
How to Use the Absolute Value Function in Excel
To learn more about using functions in Excel I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.
The Excel 2019 Bible
Microsoft. (n.d.). ABS function. Retrieved January 4, 2020, from https://support.office.com/en-us/article/abs-function-3420200f-5628-4e8c-99da-c99d7c87713c.
Crowder, J. (2020, January 4). How to Use the Absolute Value Function in Excel. Retrieved January 4, 2020, from https://youtu.be/gctQJ0rhm30.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2020 Joshua Crowder