How To Change Number Signs Using Formulas

Today I’m going to have a look at three questions which crop up time and time again on the online forums. The answers are very simple when you know how, so they should be part of every Excel user’s tool belt.

How Do I Make All Positive Numbers Negative And All Negative Numbers Positive?

So here’s a short list of numbers where we want to flip the signs. We want +2 to become -2, -1 to become +1 and so on.

Flip1

There are two quick and easy ways to do this with formulas:

  • Use the - operator to multiply the numbers by -1:

=A2*-1

  • Use the - operator to negate the numbers:

=-A2

Either way is absolutely fine and they always give the same results, although the computation under the hood slightly differs. Here’s the second one in action:

 FlipSigns

How Do I Make All Numbers Positive?

For this challenge, we want to make all the numbers positive, regardless of their existing sign. One way would be to put together some conditional logic using the IF() function:

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

So that formula says… if A2 is less than zero then negate it and give me the result; otherwise just give me the value in A2.

However, there is a slightly easier way. Excel has a function called ABS() which returns the absolute value of a number: a number without its sign.

=ABS(A2)

Let’s put the ABS() function into action:

AllPositive

How Do I Make All Numbers Negative?

The last variation is how to make all numbers negative. This one combines the techniques of the two previous challenges: first use the ABS() function to make all the numbers positive and then use the - operator to negate them.

=-ABS(A2)

AllNegative

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , . Bookmark the permalink.

2 Responses to How To Change Number Signs Using Formulas

  1. Colin,
    Nice post. Another way to change the sign of every number is to type -1 in any cell, then copy it onto the clipboard. Then select the target cells and perform Paste –> Special –> Multiply.
    Hope it helps,
    Ben a/k/a Excel Esquire
    PS: How do you generate these animations?

    Like

    • Colin Legg says:

      Hi Ben,

      Yep, that’s a good tip if you want to flip all the signs without using a formula. Thanks for the comment.
      I used cropper (which is free) to create the animated gifs on this post. Another piece of software you could use (which is not free) is Camtasia Studio.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s