## How to use the ABS function

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

#### 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:

=ABS(B3)

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:

=B3*-1

### 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:

=ABS(B3)*-1

### 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:

=SUMPRODUCT(ABS(B3:B8)*1)

### Explaining formula

Excel 365 users can use the following smaller formula:

=SUM(ABS(B3:B8))

#### 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:

=AVERAGE(ABS(B3:B8))

### 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:

=MEDIAN(ABS(B3:B8)*1)

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

### 'ABS' Function examples

The following 11 articles have formulas containing the ABS function.

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 […]

The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]

Table of Contents Find closest value How to enter an array formula Find closest value - Excel 365 Find closest […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close […]

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]

This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

The image above demonstrates an array formula in cell C25 that extracts numbers based on how far off they are […]

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

### 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 signsUse 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 OscarYou can contact me through this contact form