1. ## 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. ## Re: Negative answer to formulas (Excel 2003)

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

3. ## Re: Negative answer to formulas (Excel 2003)

or =MAX(A1-B1 ,0)

4. ## Re: Negative answer to formulas (Excel 2003)

Try:

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

5. ## Re: Negative answer to formulas (Excel 2003)

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

6. ## Re: Negative answer to formulas (Excel 2003)

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

7. ## 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. ## 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.

9. ## Re: Negative answer to formulas (Excel 2003)

Format the cell to Number, with no decimal places.

HTH

10. ## 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. ## 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
•