1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Negative Numbers

Discussion in 'Business Applications' started by cchalmers, Dec 21, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. cchalmers

    cchalmers Thread Starter

    Joined:
    Nov 10, 2003
    Messages:
    28
    I have an excel spreadsheet (2002) Someone has entered in all the numbers that are supposed to be negative numbers like this 14.56- when you try and add all the numbers all together it returns a zero value because excel doesn't understand that it is a negative number because the minus sign should be on the other side of the value.

    Does anyone know of a good idea on how to get all the - signs on the other side of the number or any quick way to make excel understand these numbers as a negative eg -14.56????

    Help!!!:eek:
     
  2. Alaric_

    Alaric_

    Joined:
    Dec 18, 2005
    Messages:
    17
    No problems mate - it is a relatively easy fix.

    First back up the workbook - you want to be able to go back if something goes awry.

    Second use auto-filter and choose custom in the column the values are in. The filter you will want to build is "contains" and a "-" sign. This will filter your list down to just those entries that are negative.

    Third select the now filtered data you want changed and go to 'Edit' and 'Replace'. In the find section put a minus sign and leave the replace section empty.

    After this you now have the positive values sitting in those cells. Before you unfilter the list you need to change them back to negative. In a nearby empty column (insert one temporarily if you have to) put the following formula (Replace A1 with whatever the top cell address is your column of values you are going to change): =A1*-1

    Now grab the little button at the bottom right hand corner of the cell you just entered the formula in and drag down. This will fill in the rest of the cells in your empty column with the negative values you are after.

    Now select those newly calculated values and select 'Edit' and 'Copy'. Then select the original values you wanted to replace and select 'Edit', 'Paste Special', and select 'Values only'

    You can now delete the extra column with the calculated data and take the autofilter off. If you did it right you will have properly formatted negatives in all the spots you should.
    If you don't, reload the backup you created and try again.

    Hope this helps you.
    Ray

    P.S. This is how I fix the problem in Excel 2000, I see by reading posts beyond mine that 2002 has some easier solutions - take care.
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
  4. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Yup, spotted it under "Numbers".

    Was writing a test macro the other day, multiple autofiltering. Entered "Filter" as a temp name & was asked if I wanted to replace the existing one. An ASAP thing, perhaps? :confused:

    Have a good Xmas yourself. :cool:
     

    Attached Files:

    • ASAP.JPG
      ASAP.JPG
      File size:
      55.4 KB
      Views:
      107
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Sounds like this 'someone' needs some lessons on how to use Excel. LOL!

    Heya Brian! Merry Christmas! :D
     
  7. johnske

    johnske

    Joined:
    Jun 23, 2004
    Messages:
    167
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/426853

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice