Using an Excel Formula to Work with Positive and Negative Numbers – 6 Examples

This is an overview.

Overview of Excel Formula for Working with Positive and Negative Numbers

Overview of Excel Formulas for Working with Positive and Negative Numbers


Example 1 – Converting Negative Numbers to Positive Numbers Using Excel Formulas

1.1 Using the Excel IF Function

Use the IF function.

There are negative values in B5:B13 under the heading Negative Number. To convert these negative values and store them in column C (Positive Number):

Dataset to Convert Negative Number to Positive Number Using Excel Formulas

Step 1: Select C5 => Enter the following formula:

=IF(B5<0, -B5, B5)

Select cell C5 and insert the intended formula

Step 2: Press Enter or Tab.

-10 is converted to 10.

After pressing Enter, you will see the output

Step 3: Hover the cursor over the bottom right corner of C5 => You will see the Fill Handle icon.

Hover the mouse cursor over the right bottom corner of cell C5 to find the Fill Handle icon

Step 4: Drag the Fill Handle to convert the rest of the negative values to positive values.

Converting Negative Number to Positive Number Using IF Function

Note

You can also double-click the Fill Handle to copy the formula to the other cells in the column.


1.2 Using the Excel ABS Function

Use the ABS function.

Step 1: Select  C5 => Use the below formula:

=ABS(B5)

Select cell C5 and insert the equation of ABS Function

Step 2: Press Enter .

-10 is converted to 10.

After pressing Enter, you will see the intended output

Step 3: Find the Fill Handle icon.

Hover the mouse cursor over the right bottom corner of cell C5 to find the Fill Handle icon

Step 4: Drag down the Fill Handle to see the result in the rest of the cells.

Converting Negative Number to Positive Number Using ABS Function


1.3 Using the Excel VBA User-Defined Function

Step 1: Go to the Developer tab => In Code, select Visual Basic.

Navigate to Developer and click on Visual Basic

Step 2: Click Visual Basic to open the VBA Editor window => Select Insert => Click Module => Enter 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

Insert the given code in module and Save

Step 3: Go back to to the sheet and select C5 => Enter the following:

=ConvertToPositive(B5)

Insert the given equation with User-defined function in cell C5

Step 4: Press Enter.

-10 is converted to 10.

After pressing Enter, you will see 10 in cell C5

Step 5: Find the Fill Handle.

Hover the cursor over the right bottom corner of cell C5 to see the Fill Handle icon

Step 6: Double-click the Fill Handle to copy the formula to the other cells.

Converting Negative Number to Positive Number Using VBA User-Defined Function

Note

If you need to convert positive to negative numbers, use the Excel IF function.

Excel Formula to convert positive to negative values:

=IF(B5>0, -B5, B5)

Converting Positive Number to Negative Number Using Excel Formulas

Read More: How to Convert Negative Value to Positive in Excel Using Formula


Example 2 – Identifying Positive and Negative Numbers Using the IF Function

To identify positive and negative numbers and place them in the Status column.

Dataset to Identify Positive and Negative Numbers Using Excel Formulas

Step 1: Select C5 => use the following formula:

=IF(B5>0, "Positive", IF(B5<0, "Negative", "Zero"))

Insert the given excel formula in cell C5

Step 2: Press Enter.

7 is  displayed as a positive number.

After pressing Enter, you will get Positive in cell C5

Step 3: Find the Fill Handle icon.

Hover the mouse cursor over the right bottom corner of cell C5 to find the Fill Handle icon

Step 4: Drag down the Fill Handle to see the result in the rest of the cells.

Identifying Positive and Negative Numbers Using IF Function

Read More: How to Make Negative Numbers Red in Excel


Example 3 – Counting Positive and Negative Numbers Using the COUNTIF Function

Use the COUNTIF function.

To count and store the value of positive numbers in D6 and negative numbers in D8:

Dataset to Count Negative and Positive Numbers Using Excel Formulas

To count the positive numbers:

Step 1: Select D5 => Enter the following formula:

=COUNTIF(B5:B13, ">0")

Select cell D5 and insert the intended formula

Step 2: Press Enter.

3 is displayed in D5, as there are only three positive numbers.

Hit Enter and see total count of Positive Numbers

To count the negative numbers:

Follow these steps:

Step 3: Select D8 =>Enter the following formula:

=COUNTIF(B5:B13, "<0")

Select cell D8 and insert the given formula

Step 4: Press Enter.

5 is displayed in D8, as there are five negative numbers.

Press Enter and you can see the total count of Positive and Negative Numbers

Read More: How to Sum Negative and Positive Numbers in Excel


Example 4 – Summing Positive and Negative Numbers with the SUMIF Function

Use the SUMIF function.

To store the summation of positive and negative numbers in D5 and D8.

Dataset to Sum Negative and Positive Numbers Using Excel Formulas

Sum the positive numbers.

Step 1: Select D5 =>Use the following formula:

=SUMIF(B5:B13, ">0")

Select cell D5 and insert the intended formula to sum positive numbers

Step 2: Press Enter.

41 is displayed in D5.

Hit Enter and see the sum of positive numbers

Sum the negative numbers.

Step 3: Select D8 =>Enter the following formula:

=SUMIF(B5:B13, "<0")

Select cell D8 and insert the intended formula to sum positive numbers

Step 4: Press Enter.

-108 is displayed in D8.

Press Enter and you can see the sum of Positive and Negative Numbers

Read More: How to Put Negative Percentage Inside Brackets in Excel


Example 5 – Separating Positive and Negative Numbers Using Excel Formulas

5.1 Using MAX and MIN Functions

Combine the MAX and MIN functions.

To keep all positive and negative numbers in columns D and E:

Dataset to Separate Positive and Negative Numbers Using Excel Formulas

Separate all positive numbers.

Step 1: Choose D5 => Enter the following formula:

=MAX(B5,0)

Choose cell D5 and insert the given formula

Step 2: Press Enter.

7 is displayed in D5.

Hit Enter and you will see the result for D5

Step 3: Find the Fill Handle.

Find the Fill Handle icon of cell D5

Step 4: Drag down the Fill Handle to see the result in the rest of the cells.

Double-click on the Fill Handle icon to see the intended result

To separate the negative numbers.

Step 5: Select E5 => Use the following formula:

=MIN(B5,0)

Choose cell E5 and insert the given formula

Step 6: Press Enter.

0 is displayed in E5.

Hit Enter and you will see the result for E5

Step 7: Find the Fill Handle.

Find the Fill Handle icon of cell E5

Step 8: Drag down the Fill Handle to see the result in the rest of the cells.

Hit Enter and You see the Positive and Negative Numbers are separated in two columns

Note

Separating positive and negative numbers with the MAX and the MIN functions inserts a 0 in the cells. To avoid it, use the IF function.


5.2 Using the IF Function

Dataset to separate positive and negative numbers using IF function

Separate positive numbers.

Step 1: Select D5 => Enter the following formula:

=IF(B5>0,B5,"")

Choose cell D5 and insert the given formula

Step 2: Press Enter.

7 is displayed in D5.

Hit Enter and you will see the result

Step 3: Find the Fill Handle.

Find the Fill Handle icon of cell D5

Step 4: Drag down the Fill Handle to see the result in the rest of the cells.

Positive numbers are separated and there are no extra zeros.

Drag the Fill Handle icon to D13 to separate positive numbers

Separate the negative numbers.

Step 5: Select E5 => Enter the following formula:

=IF(B5<0,B5,"")

Insert the given formula in cell E5

Step 6: Press Enter.

E5 is empty.

Press Enter and you can see that the E5 cell is empty

Step 7: Find the Fill Handle.

Find the Fill Handle icon of cell E5

Step 8: Drag down the Fill Handle to see the result in the rest of the cells.

Negative numbers are separated and there are no extra zeros.

Double-click on Fill Handel and you can see the Positive and Negative Numbers are separated

Read More: How to Add Brackets to Negative Numbers in Excel


Example 6 – Swapping Between Positive and Negative Numbers By Multiplying Negative – 1

To swap positive and negative numbers and place them in E5:E13:

Dataset to Swap Between Positive and Negative Numbers By Multiplying Negative 1

Step 1: Select E5 =>Enter the following formula:

=B5 * -1

Choose cell E5 and insert the given formula

Step 2: Press Enter.

7 is turned into -7 in E5.

After pressing Enter, you will see negative 7 as output

Step 3: Find the Fill Handle.

Find the Fill Handle icon of cell E5

Step 3: Drag down the Fill Handle to see the result in the rest of the cells.

All numbers are swapped.

Drag the Fill Handle icon to cell E13 and see Positive and Negative Numbers are swapped

Read More: How to Move Negative Sign at End to Left of a Number in Excel


Download Practice Workbook


Related Articles


<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo