Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: simple if/then stuff

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Springfield
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry forthis I'm new to excel and hope someone can help me.
    Problem: 2 columns of numbers/
    I need all the numbers above the value of 500
    to have 500 taken off and a U added to them.
    I have managed it but it means having to have not just the two original colums but four more.
    below is an exmaple of what i want.


    A B C D
    576 674 to 76U 174U
    128 496 to 128 128
    364 222 364 222
    971 777 471U 277U



  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF(A1>500,A1-500&"U",A1)

    Can it be as simple as that?
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Springfield
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nope Sorry tried that. That works for the number over 500 but makes the numbers under 500 negative.

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you sure? I am only changing numbers that are OVER 500, otherwise, it keeps the SAME number. Perhaps it looks like a formula you tried but is slightly different? Or perhaps you're doing something with column B that I'm not aware of.
    ~Anne Troy

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Springfield
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Doh, I must have something strange going on in that column. I tried pastin the column to a new book and tried it, it works.

    Thanks very much.

    Possibly something to do with the fact I entered the data in access then imported it.

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    Springfield
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Still hasnt worked. When I copied the entire column over it still made negative numbers. I've noticed ir's aligned to the left for some reason.

  7. #7
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Perhaps your numbers are being read as text. If so, then it is subtracting 500 from zero (probably). Do this to the numbers you're brining over:

    Put a one in any cell. Copy that cell.
    Select your *bad* numbers.
    Hit Edit-Paste special-Multiply.
    This forces Excel to see them as numbers.
    If there are spaces or something in the cell besides the text-formatted numbers, it won't work. If you've got a query or something that is getting the numbers from Access, let me know what it's doing. In your Access DB, is that column in the table formatted as text or numbers?
    ~Anne Troy

  8. #8
    New Member
    Join Date
    Feb 2002
    Location
    Springfield
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    now I've tried typing the same numbers manually into another colum doing the formula for that colum and it works!!!!!!
    I'm going to have to type the whole thing manually.

  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    Springfield
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    tried the edit paste special multiply thing. I get a column of zeros.
    I'm going to re tpye it in excel.

    Mr Gates has won again.

  10. #10
    New Member
    Join Date
    Feb 2002
    Location
    Springfield
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    "zer case is sol-ved" as Inspector Cluseau would say. My access in put thing was set as "text" and not "number". Now changed it and copied all and it works fine. Such a silly little mistake.
    Thank you everyone.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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