Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing the sign of a number

    How can I change the sign of a number in Excel? I could swear I have put a +/- button on the toolbar before, that you could click to change the sign of number in the selected cell. But I can't find it now. . . .

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing the sign of a number

    You could do it with a macro and assign it to a custom button:

    Sub Invert()
    On Error GoTo last
    ActiveCell = 0 - ActiveCell
    last:
    End Sub

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing the sign of a number

    My VBA programming is rusty, so I haven't tried that yet. I do know that you would have to check for three different cases -- if the number is > 0, < 0, or 0.

    Thanks for the hint about "ActiveCell", I didn't know about that....

    I thought there was already a button defined internally, but I can't find one.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing the sign of a number

    It depends on where and how you want to do it. If the number is in A1, then you can put the following formula in the cell where you want the number with the inverted sign:

    =A1*-1

    If you have a range of cells where you want to change the sign in the cell, then you can follow this procedure:

    1- Find an empty cell somewhere on the spreadsheet and enter -1 into the cell.
    2- Select the range of cells that you want to change the sign in.
    3- In the Edit menu select "Paste Special".
    4- In the dialong box, in the "Operation" group, select "Multiply".
    5- Press OK

    All the selected cells should have the sign changed.

    If you want to do it in VBA code, then just multiply by -1.

    Do any of these meet what you want to do?
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing the sign of a number

    The Paste Special will work great. No programming either; easier to pass on to an end user.

    (Just need to remember to Edit, Copy the -1 cell before proceeding to Step 2.)

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •