Converts negative numbers to positive numbers, in other words, the ABS function removes the sign.

1. ABS Function Syntax

ABS(number)

Back to top

2. ABS Function Arguments

Back to top

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.

Back to top

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:

Back to top

4.1 Explaining formula

Step 1 - Populate cell reference

B3*-1

returns

56*-1

Step 2 - Evaluate formula

56*-1 equals -56.

Back to top

5. How to convert all numbers to negative numbers?

Formula in cell C3:

Back to top

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.

Back to top

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.

Back to top

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

1. Copy the array formula above.
2. Double press with the left mouse button on cell D3, a prompt appears.
3. Paste it to cell C3, shortcut keys are CTRL + v.
4. Press and hold CTRL + SHIFT keys simultaneously.
5. Press Enter once.
6. 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

Back to top

8. Convert negative numbers to positive and calculate median

Array formula in cell D3:

How to enter an array formula

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.

Back to top