Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wrong result if function values over 256
very strange behavior of if function resulting in wrong result which might be
a bug. try it out at your own, and try other values just remember the same difference. a1=300 b1=303,6 c1=b1-a1 d1=3,6 e1=if(c1<=d1;"OK";"NOT OK") the result of e1 should be "OK" but its "NOT OK" a1=200 b1=203,6 c1=b1-a1 d1=3,6 e1=if(c1<=d1;"OK;"NOT OK") the result of e1 is "OK" What the hell is going on ? It does not help to format the cell's. Have i found a bug, and if it is i will call it a MAJOR BUG which could be the reson of world economic crisis ;o) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wrong result if function values over 256
In article ,
abracadabra wrote: What the hell is going on ? It does not help to format the cell's. Have i found a bug, and if it is i will call it a MAJOR BUG which could be the reson of world economic crisis ;o) This isn't a bug, it's a byproduct of using binary math that nearly every spreadsheet exhibits (granted, users who don't know how their tools work may contribute to the world economic crisis, but that's a different story..). See: http://www.cpearson.com/excel/rounding.htm What you're seeing is a small rounding error. If you need to compare numbers, it's much better to use a range. e.g.: E1: =IF(ABS(C1-D1)<0.000000001, "OK", "NOT OK") Adjust the 0.0000000001 to suit. Of course, if you need better precision than XL's 15 internal digits, you should be using a different package. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wrong result if function values over 256
On Dec 26, 2:29*pm, abracadabra
wrote: a1=300 b1=303,6 c1=b1-a1 d1=3,6 e1=if(c1<=d1;"OK";"NOT OK") the result of e1 should be "OK" but its "NOT OK" a1=200 b1=203,6 c1=b1-a1 d1=3,6 e1=if(c1<=d1;"OK;"NOT OK") the result of e1 is "OK" What the hell is going on ? The problem is that Excel uses a standard binary format to represent numbers, as do most applications. In binary, numbers with decimal fractions (and extremely large integers) cannot be represented exactly. In your example, 3.6 is represented internally exactly as 3.600000000000000088817841970012523233890533447265 625, whereas the result of 300-303.6 is represented internally exactly as 3.6000000000000227373675443232059478759765625. As you can see, Excel is correct in determining that (300-303.6)<=3.6 is false. On the other hand, the result of 200-203.6 is represented internally exactly as 3.599999999999994315658113919198513031005859375. In this case, (200-203.6)<=3.6 is true. But that is merely coincidence, an artifact of the way that binary arithmetic is performed by the hardware. The usual solution is to round expressions appropriately, where "appropriately" means subject to the requirements of your application. For your examples, ROUND(expression,13) does the trick in both cases. But again, that is coincidence. If you only need accuracy to one decimal place, use ROUND(expression,1) consistently. Caveat: Rounding is not a panacea. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wrong result if function values over 256
Errata....
On Dec 26, 11:50 pm, I wrote: the result of 300-303.6 is represented internally exactly as 3.6000000000000227373675443232059478759765625. [....] the result of 200-203.6 is represented internally exactly as 3.599999999999994315658113919198513031005859375. Doh! Of course, I should have write 303.6-300 and 203.6-200 everywhere. ----- original posting ----- On Dec 26, 11:50*pm, joeu2004 wrote: On Dec 26, 2:29*pm, abracadabra wrote: a1=300 *b1=303,6 *c1=b1-a1 * d1=3,6 e1=if(c1<=d1;"OK";"NOT OK") the result of *e1 should be "OK" but its "NOT OK" a1=200 b1=203,6 * c1=b1-a1 * d1=3,6 e1=if(c1<=d1;"OK;"NOT OK") the result of *e1 is "OK" What the hell is going on ? The problem is that Excel uses a standard binary format to represent numbers, as do most applications. *In binary, numbers with decimal fractions (and extremely large integers) cannot be represented exactly. In your example, 3.6 is represented internally exactly as 3.600000000000000088817841970012523233890533447265 625, whereas the result of 300-303.6 is represented internally exactly as 3.6000000000000227373675443232059478759765625. *As you can see, Excel is correct in determining that (300-303.6)<=3.6 is false. On the other hand, the result of 200-203.6 is represented internally exactly as 3.599999999999994315658113919198513031005859375. *In this case, (200-203.6)<=3.6 is true. *But that is merely coincidence, an artifact of the way that binary arithmetic is performed by the hardware. The usual solution is to round expressions appropriately, where "appropriately" means subject to the requirements of your application. *For your examples, ROUND(expression,13) does the trick in both cases. *But again, that is coincidence. *If you only need accuracy to one decimal place, use ROUND(expression,1) consistently. Caveat: *Rounding is not a panacea. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
wrong result if function values over 256
Hi,
I thing that larger errors by users are responsible for the world economic crisis, and if they had used Excel properly they might have gotten an inkling of what could happen to them. Really, these number are off in the 14th or 15th decimal place - 0.000000000000034 not 5,000,000,000,000 (5 trillion dollars) A lot of people bought houses far beyond theirs means and not by $.0000000000003, more like $100,000. Cheers, Shane Devenshire "abracadabra" wrote in message ... very strange behavior of if function resulting in wrong result which might be a bug. try it out at your own, and try other values just remember the same difference. a1=300 b1=303,6 c1=b1-a1 d1=3,6 e1=if(c1<=d1;"OK";"NOT OK") the result of e1 should be "OK" but its "NOT OK" a1=200 b1=203,6 c1=b1-a1 d1=3,6 e1=if(c1<=d1;"OK;"NOT OK") the result of e1 is "OK" What the hell is going on ? It does not help to format the cell's. Have i found a bug, and if it is i will call it a MAJOR BUG which could be the reson of world economic crisis ;o) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL 2000 - IF function returns wrong result from logical test | Excel Worksheet Functions | |||
Wrong result on dividing 2 values | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions |