Excel Formula for Positive and Negative Numbers (With Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of excel formula for positive and negative numbers

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.

Dataset for Excel formula for 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)

Sum all numbers positive and negatives

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)

Excel formula to show the sign of numbers

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")

IF function to show status of numbers

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

Get maximum and minimum of positive and negative values

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")

Sum positive and negative numbers separately


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")

Count positive and negative numbers separately

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)

Excel formula to get positive values only

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")

Get maximum and minimum of positive and negative values


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")

Formula to get average of positive and negative values

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

Generating random positive and negative numbers

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)

Converting random values with specific decimal points

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.

Formatting positive and negative numbers

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

Select desired format

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 …

Conditional formatting for positive numbers

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

Highlight positive values

  • So, the positive numbers are highlighted.
  • Again, select range E5:E11 and follow Conditional Formatting >> Highlight Cells Rules >> Less Than…

Conditional formatting for negative numbers

  • 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.

Highlight negative values

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.

Enter into VBA window

  • 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

 

Paste VBA code into the module

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

Get all positive numbers

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

Bishawajit Chakraborty
Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo