How to use the ABS function
What is the ABS function?
The ABS function converts negative numbers to positive numbers, in other words, the ABS function removes the sign and returns the absolute value.
When is it useful to calculate the absolute value?
The absolute value in math is expressed like this: |-12| = 12 The absolute value is denoted by vertical bars on each side of the number. The ABS function lets you calculate the non-negative value.
Table of Contents
- ABS Function Syntax
- ABS Function arguments
- ABS function example
- How to convert a negative number to positive numbers and vice versa?
- How to convert all numbers to negative numbers?
- Convert negative numbers to positive and calculate the sum
- Convert negative numbers to positive and calculate the average
- Convert negative numbers to positive and calculate the median
- Get Excel *.xlsx file
1. ABS Function Syntax
ABS(number)
2. ABS Function Arguments
| number | The number you want to remove the sign from. |
3. ABS Function example
The ABS function converts a negative number to a positive number, in other words, it removes the sign from a number.
Formula in cell C3:
The ABS function returns #VALUE if you use a text string, however, it returns 1 for boolean value TRUE and 0 (zero) for FALSE.
4. How to convert a negative number to positive numbers and vice versa?
The formula in cell C3 converts negative numbers to positive numbers and positive numbers to negative numbers.
Formula in cell C3:
4.1 Explaining formula
Step 1 - Populate cell reference
B3*-1
returns
56*-1
Step 2 - Evaluate formula
56*-1 equals -56.
5. How to convert all numbers to negative numbers?
Formula in cell C3:
Explaining formula
Step 1 - Calculate the absolute number
ABS(B3)
becomes
ABS(56)
and returns 56.
Step 2 - Multiple with -1
The asterisk lets you multiply numbers in an Excel formula.
ABS(B3)*-1
becomes
56*-1
and returns -56.
6. Convert negative numbers to positive and calculate sum
The following formula converts negative numbers to positive and then sums the numbers. 56 + 24 + 20 + 23 + 52 + 70 equals 245.
Formula in cell D3:
Explaining formula
Excel 365 users can use the following smaller formula:
Step 1 - Convert to absolute numbers
ABS(B3:B8)
becomes
ABS({56; 24; -20; -23; 52; -70})
and returns
{56; 24; 20; 23; 52; 70}.
Step 2 - Add absolute numbers and return a total
The SUM function adds numbers and then returns a total.
SUM(ABS(B3:B8))
becomes
SUM({56; 24; 20; 23; 52; 70})
and returns 245.
7. Convert negative numbers to positive and calculate average
The formula in cell D3 removes the negative signs from the numbers in cell range B3:B8 and then calculates an average.
Array formula in cell D3:
7.1 How to enter an array formula
- Copy the array formula above.
- Double press with the left mouse button on cell D3, a prompt appears.
- Paste it to cell C3, shortcut keys are CTRL + v.
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
The formula bar shows a beginning and ending curly bracket, don't enter these characters yourself.
7.2 Explaining formula
Step 1 - Convert numbers to absolute numbers
ABS(B3:B8)
becomes
ABS({56; 24; -20; -23; 52; -70})
and returns
{56; 24; 20; 23; 52; 70}.
Step 2 - Calculate an average
The AVERAGE function calculates the average of numbers in a cell range.
AVERAGE(number1, [number2], ...)
AVERAGE (ABS(B3:B8))
becomes
AVERAGE ({56; 24; 20; 23; 52; 70})
and returns approx. 40.83
8. Convert negative numbers to positive and calculate median
Array formula in cell D3:
Explaining array formula
Step 1 - Convert to absolute numbers
ABS(B3:B8)
becomes
ABS({56; 24; -20; -23; 52; -70})
and returns
{56; 24; 20; 23; 52; 70}.
Step 2 - Calculate median
The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a group of numbers.
If the group contains an even number of numbers the MEDIAN function calculates the average of the two numbers in the middle.
MEDIAN(ABS(B3:B8))
becomes
MEDIAN({56; 24; 20; 23; 52; 70})
and returns 38. 52 + 24 equals 76. 76 / 2 equals 38.
Useful resources
ABS function - Microsoft
ABSOLUTE Function in Excel (ABS)
Absolute value - Wikipedia
'ABS' function examples
This article demonstrates Excel formulas that calculate complete weeks between two given dates and weeks and days between two given […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
Functions in 'Math and trigonometry' category
The ABS function function is one of many functions in the 'Math and trigonometry' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form