Results 1 to 11 of 11

20080710, 06:02 #1
 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 A1B1 or 1020 appear as 0 not 10 in a cell.

20080710, 06:05 #2
 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(A1B1<=0,0,A1B1)

20080710, 06:08 #3
 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(A1B1 ,0)
John ... I float in liquid gardens
UTC 7ąDS

20080710, 06:08 #4
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Negative answer to formulas (Excel 2003)
Try:
=IF(A1B1<0,0,A1B1)

20080710, 06:52 #5
 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.11e15.

20080710, 07:00 #6
 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.

20080710, 07:23 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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?

20080710, 07:58 #8
 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.105e15 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.

20080710, 08:06 #9
 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

20080710, 08:07 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20080710, 08:10 #11
 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