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

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.

### 'ABS' function examples

The following 12 articles contain the ABS function.

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

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

This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]

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 this article

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