Negative and positive numbers are very common in Excel for any kind of calculation. We can use those numbers using formulas and other features. Dealing with negative numbers is tricky work. In this article, we will discuss the Excel formula for positive and negative numbers with different examples.

We can see some numbers with brackets in the **Price Change** column. Those numbers are negative. When we get negative numbers in Excel, by default a bracket adds with negative numbers in Excel in **Accounting** format.

**Table of Contents**hide

## Explanation of Positive and Negative Numbers?

Positive and negative numbers are opposite to each other. As someone says, the price changed by **-$30**, which means the price of the product decreased by **$30**. On the other hand, a price change of **$30** means the price increased by **$30**. But the magnitude of positive and negative numbers are the same. In the previous example, both casesâ€™ value is **$30**.

In this section, we discuss some examples showing the basic formula for positive and negative numbers. Those include: how to sum, determine signs, identify numbers with comments, and reverse the positive and negative numbers.

**1. Using the Excel SUM Function to Get the Sum of All Positive and Negative Numbers**

In the first example, we will calculate the **sum of all positive and negative numbers** using **the SUM function**.

- Go to
**cell E12**and apply the following formula.

`=SUM(E5:E11)`

Here, we did not separate the positive and negative values.

**2. Excel Formula for Positive and Negative Numbers: Determine Signs of Numbers**

One of the basic tasks when working with positive and negative numbers is to determine the sign of the given number. For that, **the SIGN function** is the solution.

The **SIGN** function returns **1** for positives and **-1** for negatives.

- Go to
**cell F5**and paste the following formula. - Then, drag the
**Fill Handle**icon.

`=SIGN(E5)`

We get the respective sign of each number.

**3. Determine the Positive and Negative Numbers with Comments**

In this example, we will identify each positive and negative number with a comment. For that, we are using **the IF function**. We set a condition whether the values are greater than **0 **or not. If **True **print **Positive **and for **False **print **Negative**.

- Insert the following formula on
**cell F5**and drag the**Fill HandleÂ**icon.

`=IF(E5>0,"Positive","Negative")`

This returns the status of each number.

**Read More: ****[Fixed!] Excel Not Adding Negative Numbers Correctly**

**4. Excel Formula for Positive and Negative Numbers: Reverse Number Signs**

Sometimes we need to get the reverse value of a range or a column. You can learn how to do this from this example. The process is very simple, just multiply the cell range by **-1**.

- Put the following formula on
**cell F5**and drag the**Fill HandleÂ**icon.

`=E5*-1`

We get the reverse value for each cell.

**More Examples of Excel Formula for Positive and Negative Numbers**

In this section, we will show some advanced formulas using positive and negative numbers. Like, we calculate the sum and count based on condition.

**1. Get the Sum of Positive and Negative Numbers Separately in Excel**

Previously, we determined the sum of all positive and negative numbers combinedly. But now, we want to **add the negative numbers** and positive numbers separately by taking into account IF criteria.

For this, we will use an advanced function that is **SUMIF** function. **The SUMIF function** works based on condition.

- Apply two formulas respectively on
**cells E13**and**E14**.

The formula for positive on **cell E13:**

`=SUMIF($E$5:$E$11,">0")`

The formula for negative on **cell E14:**

`=SUMIF($E$5:$E$11,"<0")`

**2. Excel Formula for Counting Positive and Negative Numbers**

In this example, we will count the positive and negative numbers using **the COUNTIF function**. The **COUNTIF** function works based on condition.

- Insert formulas based on the
**COUNTIF**function on**cells E13**and**E14**.

Formula to count positives on **cell E13**:

`=COUNTIF($E$5:$E$11,">0")`

Formula to count negatives on **cell E14**:

`=COUNTIF($E$5:$E$11,"<0")`

**Read More: ****How to Count Negative Numbers in Excel**

**3. Converting Negative Numbers into Positive Using Excel ABS Function**

In this example, we will use **the ABS function** to determine the absolute value of any number. We will get only positive values for any positive or negative numbers.

- Insert the following formula on cell
**F5**and drag the**Fill HandleÂ**icon.

`=ABS(E5)`

We get only positive values here.

**4. Excel Formula for Positive and Negative Numbers: Get Maximum and Minimum Numbers Separately**

We can easily get the maximum and minimum number of a range using the **MAX** and **MIN** functions. But here we are working with both positive and negative numbers and we want to get the maximum and minimum of both positive and negative numbers separately.

For that, we will use the **MAXIFS** and **MINIFS** functions. Both of the functions work with conditions.

- We used the following formulas on
**cells E13**to**E16**.

The formula for the maximum positive value on **cell E13**:

`=MAXIFS($E$5:$E$11,$E$5:$E$11,">0")`

This formula is for the maximum negative value on **cell E14**:

`=MAXIFS($E$5:$E$11,$E$5:$E$11,"<0")`

The formula for the minimum positive value on **cell E15**:

`=MINIFS($E$5:$E$11,$E$5:$E$11,">0")`

The formula for the minimum negative value on **cell E16**:

`=MINIFS($E$5:$E$11,$E$5:$E$11,"<0")`

**5. Calculate the Average of Positive and Negative Numbers**

Here, we want to calculate the average values of positive and negative values. Due to both positive and negative numbers, we use **the AVERAGEIF function**.

- Apply the following formula to
**cells E13**and**E14**.

The formula for **positive** numbers:

`=AVERAGEIF($E$5:$E$11,">0")`

The formula for **negative** numbers:

`=AVERAGEIF($E$5:$E$11,"<0")`

We get the average of positive and negative numbers.

**6. Use of the ROUND Function to Get Positive and Negative Values with Specific Decimal Places**

In this example, we want to get positive and negative numbers with a certain decimal point using a formula.

1st generated some random positive and negative numbers within the range of **-30** to **80**. For that, we applied a formula based on **the RAND function**. Then, **the ROUND function** converts those random numbers to a value with a 5 decimal point.

**ðŸ“Œ ****Steps:**

- Apply the following formula to
**cell C5**.

`=RAND()*(-80+30)-30`

Here, we generated random positive and negative numbers without declaring decimal points.

- Now, apply the formula on
**cell D5**based on the**ROUND**function to set up the decimal points of the generated random numbers.

`=ROUND(C5,3)`

Here, all random numbers are with **3** decimal places.

**Tips and Tricks to Work with Positive and Negative Numbers in Excel**

In this section, we will discuss some tips and tricks that will be helpful for you when working with positive and negative numbers in Excel. You can make **negative numbers red** or show in different ways by formatting them.

**1. Format Positive and Negative Numbers in Excel**

First, we will show how to format positive and negative numbers in Excel for better presentation and understanding.

**ðŸ“Œ ****Steps:**

**1st**select the range of numbers that is**E5:E11**.- Press
**Ctrl+1**from the keyboard.

**Format Cells**window appears. We can see the Negative numbers section in this window.

Now, you can select any of the formats. Like numbers with a negative sign, or red colored numbers, etc.

**2. Highlight Positive and Negative Numbers Using Excel Conditional Formatting**

Another interesting way to present positive and negative numbers in Excel is to use Conditional Formatting to highlight the numbers differently.

**ðŸ“Œ ****Steps:**

**1st**select**range E5:E11**.- Then follow
**Conditional Formatting >> Highlight Cells Rules >> Greater Than â€¦**

- The
**Greater Than**window appears. - Set value
**0**with highlighting color and press**OK**.

- So, the positive numbers are highlighted.
- Again, select
**range E5:E11**and follow**Conditional Formatting >> Highlight Cells Rules >> Less Thanâ€¦**

**Less Than**window appears.- To highlight cells with negative values, set
**0**in this window and choose a color to highlight those cells. Lastly, press the**OKÂ**button.

So, the positive and negative numbers are highlighted with different colors.

## How to Convert Negative Numbers to Positive with a Single Click (VBA without Excel Formula)

VBA is an interesting feature of Excel. When we need to perform a specific task repeatedly, we apply VBA. Here, we will show how we can convert any positive or negative numbers into positive ones.

**ðŸ“Œ ****Steps:**

- Select range
**E5:E11**. - Go to the
**Sheet Name**section at the bottom of the sheet. - Choose the
**View Code**option from the**Context Menu**.

- Follow
**Insert**tab**>> Module**. - Then, paste the following VBA code on the module.

```
Sub Convert_Positive()
Â Â Â Dim Input_range As Range
Â Â Â Set Input_range = Selection
Â Â Â For Each Cell In Input_range
Â Â Â Â Â Â Â If Cell.Value < 0 Then
Â Â Â Â Â Â Â Â Â Â Â Cell.Offset(0, 1).Value = Cell.Value * -1
Â Â Â Â Â Â Â Else
Â Â Â Â Â Â Â Â Â Â Â Cell.Offset(0, 1).Value = Cell.Value
Â Â Â Â Â Â Â End If
Â Â Â Next Cell
End Sub
```

- Now, run the code by pressing the
**F5**button and look at the worksheet.

All positive numbers are shown in the dataset.

**Code Breakdown**

**Dim Input_range As Range**

Declare a variable with a specific type.

**Set Input_range = Selection**

Set the value of the variable from the selection.

- For Each Cell In Input_range

Apply a for loop for each cell of the variable.

**If Cell.Value < 0 Then, Cell.Offset(0, 1).Value = Cell.Value * -1, Else, Cell.Offset(0, 1).Value = Cell.Value**

If the cell value is smaller than **0** then multiply that by **-1** and print that value on the corresponding cell of the next column using the Offset function.

And if this argument is not true then, simply paste the original value into the corresponding cell of the next column.

## Frequently Ask Questions

**1. Can we convert any number into a positive?**

**Ans**: Yes, you can convert any number into a positive in two ways: Excel formula and **VBA** macro. You can use the **ABS** function in the **following way** for positives. Or you can go to this **section **for VBA macro to get positives.

**2. What will happen if you add positive and negative numbers?**

Ans: If we add both negative and positives in Excel, the negative values will be subtracted from the positive values.

**3. How to make any number negative?**

For this, we can simply use the **ABS** function. First, the **ABS **function returns the positive values of all numbers, and then multiple them by** -1**. So, the formula will be like this:

`=ABS(A1)*-1`

Here, **A1** contains any positive or negative number.

**Download Practice Workbook**

You may download the following Excel workbook for better understanding and practice it by yourself.

**Conclusion**

In this article, we discussed the Excel formula for positive and negative numbers using different Examples. We have shown basic and advanced formulas that can be applied to both positive and negative numbers in Excel.

Basic formulas like sum, determining sign, identifying the numbers with a comment, and getting the reverse of those numbers are discussed here. Those formulas can work without any complexity.

In the advanced formulas, we separated the positives and negatives and apply different functions. We discussed how to sum, count, maximum, minimum, average, etc. for both positive and negative numbers.

We also added some tricks on how to handle positive and negative numbers in Excel. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.

## Related Articles

**How to Add Brackets to Negative Numbers in Excel****How to Put a Negative Number in Excel Formula****Excel Formula to Return Zero If Negative Value is Found****How to Show Negative Numbers in Excel****Excel Negative Numbers in Brackets and Red****How to Convert Negative Value to Positive in Excel Using Formula****How to Make a Group of Cells Negative in Excel****Excel Formula If Cell Contains Negative Number****Excel Formula to Return Blank If Cell Value Is Negative****How to Change Positive Numbers to Negative in Excel**