Bishawajit Chakraborty
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.
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
