Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative answer to formulas (Excel 2003)

    This is very simply but I can't figure it out. How do I use a formula say A1-B1 or 10-20 appear as 0 not -10 in a cell.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative answer to formulas (Excel 2003)

    =if(A1-B1<=0,0,A1-B1)

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Negative answer to formulas (Excel 2003)

    or =MAX(A1-B1 ,0)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative answer to formulas (Excel 2003)

    Try:

    =IF(A1-B1<0,0,A1-B1)

  5. #5
    New Lounger
    Join Date
    Jul 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative answer to formulas (Excel 2003)

    Thanks but what if the answer is 0 I get a value of 7.11e-15.

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative answer to formulas (Excel 2003)

    I have tried all three methods and don't get that either way. See attached.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative answer to formulas (Excel 2003)

    Welcome to Woody's Lounge!

    Could you post a small sample workbook in which you get such a result?

  8. #8
    New Lounger
    Join Date
    Jul 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative answer to formulas (Excel 2003)

    I have attached a sheet with my problem. One sheet has John Smith CEU hrs. The results have to be multiplied by 10 which is included in cell h21. I then linked cell h21 to the Staff sheet in cell D6. When I apply the formula in cell F6 it gives me 7.105e-15 result. I think the problem is with the multiplying by 10. Love to see what you come up with since I have playing with this for an hour.
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative answer to formulas (Excel 2003)

    Format the cell to Number, with no decimal places.

    HTH
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative answer to formulas (Excel 2003)

    The problem is caused by very small rounding errors. Excel displays numbers using the decimal system, but it stores and processes them using the binary system (only 0 and 1). Many decimal numbers cannot be represented exactly in binary, causing small errors.
    You can get around it by rounding the result of the formula, for example to 1 decimal place:

    =ROUND(SUM(H10:H20)*10,1)

    in cell H21 on the John Smith sheet. This will get rid of the rounding error.

  11. #11
    New Lounger
    Join Date
    Jul 2008
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative answer to formulas (Excel 2003)

    Thanks for your help. I can't believe the answer was that simple!

    Cheers

Posting Permissions

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