In this Excel tutorial, you will learn how to work with positive and negative numbers using Excel Formula. We will discuss how to do these using Excel formulas:
– Convert Negative Numbers to Positive Numbers
– Identify Positive and Negative Numbers
– Count Positive and Negative Numbers
– Sum Positive and Negative Numbers
– Separate Positive and Negative Numbers
– Swap Between Positive and Negative Numbers
Note: While writing this article, I used Microsoft Excel 365. But all these methods can be applicable in any Excel version.
Various fields like finance, budget-making, scientific research, and project management must frequently handle positive and negative numbers. For example, Inventory management relies on tracking incoming stock (positive) and outgoing stock (negative) accurately. In scientific research, data often includes both positive and negative values. Excel offers many functions like IF, COUNTIF, SUMIF, MAX, and MIN for working with positive and negative numbers.
Overview of Excel Formulas for Working with Positive and Negative Numbers
1. Converting Negative Numbers to Positive Numbers Using Excel Formulas
Converting negative numbers to positive numbers in Excel can be done using the Excel IF Function, Excel ABS function, and Excel VBA code.
1.1 Using Excel IF Function
In this section, you will learn how to convert negative into positive values using the IF function.
Suppose, in the following image, you see some negative values in the range B5:B13 under the heading Negative Number. I will convert these negative values and store them in column C, named Positive Number, using the Excel IF function.
Follow these steps:
Step 1: Select cell C5 => Insert the following formula:
=IF(B5<0, -B5, B5)
Step 2: Press the Enter or Tab button, and we will find -10 is converted to 10.
Step 3: Hover the cursor over the bottom right corner of cell C5 => You will see the Fill Handle icon.
Step 4: Drag the Fill Handle icon to cell C13 to convert the rest of the negative values to positive values.
You can also double-click on the Fill Handle icon to copy the formula to other cells in the column instead of dragging.
1.2 Using Excel ABS Function
We can also convert the negative values to positive values in Excel using the ABS function.
Excel ABS function converts negative values to positive values and keeps the positive values unchanged.
Follow these steps:
Step 1: Select cell C5 => Insert the below formula:
=ABS(B5)
Step 2: Press the Enter button, and we will see -10 is converted to 10.
Step 3: Likewise, find the Fill Handle icon by hovering over the button in the right corner of cell C5.
Step 4: Drag the Fill Handle icon to cell C13 to convert the rest of the negative to positive values.
1.3 Using Excel VBA User-Defined Function
We can create an Excel VBA User-defined function to convert negative to positive values.
A user-defined function will be helpful when working on automation tasks or VBA projects.
Follow these steps:
Step 1: Navigate to the Developer tab => In the Code group, you will find the Visual Basic command.
Step 2: Click on Visual Basic to open the VBA Editor window => Hover over Insert => Click on Module => Paste the following code in the module, and Save.
Function ConvertToPositive(ByVal num As Double) As Double
   If num < 0 Then
       ConvertToPositive = -num
   Else
       ConvertToPositive = num
   End If
End Function
Step 3: Return to the sheet and select cell C5 => insert the following equation:
=ConvertToPositive(B5)
Step 4: Press the Enter key => You will see -10 is converted to 10.
Step 5: Find the Fill Handle icon by hovering over the button in the right corner of cell C5.
Step 6: Double-click on the Fill Handle icon to copy the formula in other cells.
If you need to convert the positive to negative numbers, you can use the Excel IF function.
Excel Formula to convert positive to negative values:
=IF(B5>0, -B5, B5)
Read More:Â How to Convert Negative Value to Positive in Excel Using Formula
2. Identifying Positive and Negative Numbers Using IF Function
We can identify whether a number is positive or negative using the Excel IF function.
Assume, like the following image, we have some values in the range B5:B13 under the Number column. We will identify them and put decisions in the Status column.
Follow these steps:
Step 1: Select the cell C5 => insert the following formula:
=IF(B5>0, "Positive", IF(B5<0, "Negative", "Zero"))
Step 2: Press the Enter key => You will see Positive as 7 is a positive number.
Step 3: Hover over the cursor in the bottom right corner to find the Fill Handle icon.
Step 4: Double-click on Fill Handle to display the proper status of other numbers.
Read More: How to Make Negative Numbers Red in Excel
3. Counting Positive and Negative Numbers Using COUNTIF Function
Throughout this context, you will learn how to count positive and negative numbers using the COUNTIF function.
Assume I have some numbers in the range B5:B13 under the Number column. In cell D6, I will count the positive numbers and store the value. For negative numbers, I will use cell D8.
First, we will count the positive number within the range B5:B13.
Follow these steps:
Step 1: Select cell D5 => Insert the following formula:
=COUNTIF(B5:B13, ">0")
Step 2: Press the Enter key => You will see 3 in D5, as there are only three positive numbers.
Now, we shall count the negative numbers using Excel Formula.
Follow these steps:
Step 3: Choose cell D8 => Insert the following formula:
=COUNTIF(B5:B13, "<0")
Step 4: Press the Enter key => You can see 5 in D8 as there are five negative numbers.
Read More: How to Sum Negative and Positive Numbers in Excel
4. Summing Positive and Negative Numbers with SUMIF Function
Here, you will learn how to sum positive and negative numbers using the SUMIF function.
I will use the same dataset. However, in cells D5 and D8, I will store the summation of positive and negative numbers, respectively.
First, we will find the summation of the positive numbers within the range B5:B13.
Follow these steps:
Step 1: Select cell D5 => Insert the following formula:
=SUMIF(B5:B13, ">0")
Step 2: Press the Enter key => You will see 41 in cell D5.
Now, we shall determine the summation of the negative numbers.
Follow these steps:
Step 3: Select cell D8 => Insert the following formula:
=SUMIF(B5:B13, "<0")
Step 4: Press Enter => You will see -108 in cell D8.
Read More:Â How to Put Negative Percentage Inside Brackets in Excel
5. Separating Positive and Negative Numbers Using Excel Formulas
We can separate positive and negative numbers by combining the MAX and MIN functions. Separating of positive and negative numbers can also be done by using the IF function.
5.1 Using MAX and MIN Functions
Here, you will learn how to separate positive and negative numbers by combining the MAX and MIN functions.
Suppose I have some numbers in the range B5:B13 under the Number title. I will keep all the positive and negative numbers in columns D and E under the title Positive Number and Negative Number, respectively.
First, we will separate all the positive numbers using the Excel MAX function.
Follow these steps:
Step 1: Choose cell D5 => Insert the following formula:
=MAX(B5,0)
Step 2: Press Enter => You will see 7 in cell D5.
Step 3: Find the Fill Handle icon by hovering the cursor over the bottom right corner of cell D5.
Step 4: Double-click on Fill Handle to copy the formula in other cells => You can see positive numbers are separated.
Now, we will separate the negative numbers.
Follow these steps:
Step 5: Select cell E5 => Insert the following formula:
=MIN(B5,0)
Step 6: Press Enter => You will 0 in cell E5.
Step 7: Find the Fill Handle by hovering the bottom right corner of cell E5.
Step 8: Double-click on Fill Handle to copy the formula to other cells => You can see the negative numbers are separated as well.
As you can see, separating positive and negative numbers by using MAX and MIN functions inserts a 0 in the cells. If you want to avoid these extra 0, you must go through the following method. Using the IF function will overcome this issue.
5.2 Using IF Function
We can also separate positive and negative numbers by using the IF function. Unlike the previous method, we will avoid the extra 0 here, and thus, the sheet looks better.
I am using the same dataset for better understanding.
Firstly, we will separate positive numbers using the Excel IF Function.Â
Follow the steps:
Step 1: Select cell D5 => Insert the following formula:
=IF(B5>0,B5,"")
Step 2: Press Enter => You see 7 in cell D5.
Step 3: Hover the mouse cursor over the bottom right corner of cell D5 to find the Fill Handle.
Step 4: Drag the Fill Handle icon to cell E13 => You will see the positive numbers are separated and there are no extra zeros.
Now, we shall separate negative numbers.
Follow the steps.
Step 5: Choose cell E5 => Insert the following formula:
=IF(B5<0,B5,"")
Step 6: Press Enter => You will see that the cell E5 is empty.
Step 7: Find the Fill Handle icon by hovering over the bottom right of cell E5:
Step 8: Drag the Fill Handle icon to cell E13 => You will see negative numbers are separated and there are no extra zeros.
Read More:Â How to Add Brackets to Negative Numbers in Excel
6. Swapping Between Positive and Negative Numbers By Multiplying Negative 1
Swapping a positive to a negative number and a negative to a positive number can be done by multiplying -1.
Assume I have some numbers in the range B5:B13 under the Number titled. And their current status in the range C5:C13, whether a number is positive, negative or zero. I will swap these numbers and put them in the range E5:E13 under the Swapped Number title.
Follow these steps:
Step 1: Select cell E5 => Insert the following formula:
=B5 * -1
Step 2: Press Enter => You will see 7 is turned into -7 in cell E5.
Step 3: Find the Fill Handle icon by hovering over the bottom right of cell E5.
Step 3: Double-click on Fill Handle to copy the formula down => You can see all the numbers are swapped.
Read More: How to Move Negative Sign at End to Left of a Number in Excel
Download Practice Workbook
I have practiced all the methods within the .xlsx file except for VBA. While using VBA, I worked in the .xlsm file.
So, in this Excel tutorial, We present several Excel formulas for working with positive and negative numbers. For example, IF, ABS and VBA for converting positive to negative numbers, IF, COUNTIF and SUMIF for identifying, counting, and summing positive and negative numbers, and swapping and separating positive and negative numbers using Excel formulas. Do not hesitate to leave a comment if you have any questions.
Related Articles
- Excel Negative Numbers in Brackets and RedÂ
- How to Put Parentheses for Negative Numbers in Excel
- How to Change Positive Numbers to Negative in Excel
<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
