If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
An oddity if not a bug
A1:
=FACT(9)/FACT(6)/FACT(3) returns 84, as it should A2: =COMBIN(9,3) returns 84, as it should A3: =A1=A2 returns TRUE A4: =A1-A2 returns 0 A5: =MOD(A4,1) returns 0 A6: =MOD(A1,1) returns 0 A7: =MOD(A2,1) returns -1.42109E-14 So the @#$% fudge factor strikes again, or does it? Shouldn't it affect the evaluated value of cell A2 with the final result for A2 an integer? This shouldn't be. If cell A4 is zero with no remainder in A5, why should A7 have a remainder? Same results in 2003 and 2007. |
#2
|
|||
|
|||
An oddity if not a bug
Don't know the reason but I've read posts by Jerry Lewis that cover this:
=A1-A2 = 0 But: =(A1-A2)=0 returns FALSE So you'd think that A5: =MOD(A4,1) should have a remainder. Or is that the fudge factor in effect? Biff "Harlan Grove" wrote in message oups.com... A1: =FACT(9)/FACT(6)/FACT(3) returns 84, as it should A2: =COMBIN(9,3) returns 84, as it should A3: =A1=A2 returns TRUE A4: =A1-A2 returns 0 A5: =MOD(A4,1) returns 0 A6: =MOD(A1,1) returns 0 A7: =MOD(A2,1) returns -1.42109E-14 So the @#$% fudge factor strikes again, or does it? Shouldn't it affect the evaluated value of cell A2 with the final result for A2 an integer? This shouldn't be. If cell A4 is zero with no remainder in A5, why should A7 have a remainder? Same results in 2003 and 2007. |
#3
|
|||
|
|||
An oddity if not a bug
So you'd think that A5: =MOD(A4,1) should have a remainder. Or is that the
fudge factor in effect? There it is: =MOD((A1-A2),1) 1.4210854715202E-14 Biff "Biff" wrote in message ... Don't know the reason but I've read posts by Jerry Lewis that cover this: =A1-A2 = 0 But: =(A1-A2)=0 returns FALSE So you'd think that A5: =MOD(A4,1) should have a remainder. Or is that the fudge factor in effect? Biff "Harlan Grove" wrote in message oups.com... A1: =FACT(9)/FACT(6)/FACT(3) returns 84, as it should A2: =COMBIN(9,3) returns 84, as it should A3: =A1=A2 returns TRUE A4: =A1-A2 returns 0 A5: =MOD(A4,1) returns 0 A6: =MOD(A1,1) returns 0 A7: =MOD(A2,1) returns -1.42109E-14 So the @#$% fudge factor strikes again, or does it? Shouldn't it affect the evaluated value of cell A2 with the final result for A2 an integer? This shouldn't be. If cell A4 is zero with no remainder in A5, why should A7 have a remainder? Same results in 2003 and 2007. |
#4
|
|||
|
|||
An oddity if not a bug
Biff wrote...
So you'd think that A5: =MOD(A4,1) should have a remainder. Or is that the fudge factor in effect? There it is: =MOD((A1-A2),1) 1.4210854715202E-14 .... I hadn't thought of that, but this is still a bug. COMBIN should *only* return integers or error values. If both its arguments are numbers, it'll truncate them to integers, e.g., COMBIN(7.2,2.9) returns the same result as COMBIN(5,2). If its arguments are integers, then numerically it can only return integers. If its result would be beyond Excel's capacity, e.g., COMBIN(1030,515), no problem having it return #NUM!. If its result would take more than 15 decimal digits, no problem that it'd be only approximately correct to 15 decimal digits (and it'd also be an integer). But when it could easily be represented in 15 or fewer decimal digits, there's no excuse for it not to be an integer. FWIW, the standard approach when N gets large enough that FACT(N) exceeds 15 decimal digits is to use EXP(GAMMALN(N+1)-GAMMALN(k+1)-GAMMALN(N-k+1)), but in the case of N=9 and k=3, this returns 84.0000000106965, while (COMBIN(9,3)-84) is negative. Leaves me wondering how Microsoft is calculating this. |
#5
|
|||
|
|||
An oddity if not a bug
this is still a bug. COMBIN should *only*
return integers or error values. I agree and had always assumed as much. This little exercise proves otherwise. =MOD((COMBIN(9,3)-84),1) Returns 1. Biff "Harlan Grove" wrote in message oups.com... Biff wrote... So you'd think that A5: =MOD(A4,1) should have a remainder. Or is that the fudge factor in effect? There it is: =MOD((A1-A2),1) 1.4210854715202E-14 ... I hadn't thought of that, but this is still a bug. COMBIN should *only* return integers or error values. If both its arguments are numbers, it'll truncate them to integers, e.g., COMBIN(7.2,2.9) returns the same result as COMBIN(5,2). If its arguments are integers, then numerically it can only return integers. If its result would be beyond Excel's capacity, e.g., COMBIN(1030,515), no problem having it return #NUM!. If its result would take more than 15 decimal digits, no problem that it'd be only approximately correct to 15 decimal digits (and it'd also be an integer). But when it could easily be represented in 15 or fewer decimal digits, there's no excuse for it not to be an integer. FWIW, the standard approach when N gets large enough that FACT(N) exceeds 15 decimal digits is to use EXP(GAMMALN(N+1)-GAMMALN(k+1)-GAMMALN(N-k+1)), but in the case of N=9 and k=3, this returns 84.0000000106965, while (COMBIN(9,3)-84) is negative. Leaves me wondering how Microsoft is calculating this. |
#6
|
|||
|
|||
An oddity if not a bug
Biff wrote...
this is still a bug. COMBIN should *only* return integers or error values. I agree and had always assumed as much. This little exercise proves otherwise. =MOD((COMBIN(9,3)-84),1) Returns 1. .... Actually, it returns 0.999999999999986. And don't get me started on MOD. With all boils in need of lancing in Excel, what does Microsoft do? Give it a nose job. |
#7
|
|||
|
|||
An oddity if not a bug
With all boils in need of lancing in Excel, what does Microsoft do?
Enhance the charting "experience" ? Did you see that post at http://blogs.msdn.com/excel/default.aspx ? A "user experience designer" ? WTF is that? That's what MS is interested in. Zen fluff! Biff "Harlan Grove" wrote in message ups.com... Biff wrote... this is still a bug. COMBIN should *only* return integers or error values. I agree and had always assumed as much. This little exercise proves otherwise. =MOD((COMBIN(9,3)-84),1) Returns 1. ... Actually, it returns 0.999999999999986. And don't get me started on MOD. With all boils in need of lancing in Excel, what does Microsoft do? Give it a nose job. |
#8
|
|||
|
|||
An oddity if not a bug
But if A8 = COMBIN(9,3)-84 it appears to properly return 0
and if A9 = MOD(A8,1) it also appears to properly return 0. "Harlan Grove" wrote: Biff wrote... this is still a bug. COMBIN should *only* return integers or error values. I agree and had always assumed as much. This little exercise proves otherwise. =MOD((COMBIN(9,3)-84),1) Returns 1. .... Actually, it returns 0.999999999999986. And don't get me started on MOD. With all boils in need of lancing in Excel, what does Microsoft do? Give it a nose job. |
#9
|
|||
|
|||
An oddity if not a bug
That's the "fudge factor" Harlan was talking about.
The result is not EXACTLY zero but the difference is so extremely small that Excel "fudges" the result to be 0. But if A8 = COMBIN(9,3)-84 it appears to properly return 0 Try this: =(COMBIN(9,3)-84)=0 Jerry Lewis has explained that a test for true equality must be done this way. Look for some of his posts where he gets into great detail about this. It's really complex and quite educational. Biff "JMB" wrote in message ... But if A8 = COMBIN(9,3)-84 it appears to properly return 0 and if A9 = MOD(A8,1) it also appears to properly return 0. "Harlan Grove" wrote: Biff wrote... this is still a bug. COMBIN should *only* return integers or error values. I agree and had always assumed as much. This little exercise proves otherwise. =MOD((COMBIN(9,3)-84),1) Returns 1. .... Actually, it returns 0.999999999999986. And don't get me started on MOD. With all boils in need of lancing in Excel, what does Microsoft do? Give it a nose job. |
#10
|
|||
|
|||
An oddity if not a bug
Thanks for the suggestion, it is educational.
From one of Jerry's posts, it seems that excel may/may not apply a fudge factor when nesting functions -or am I misunderstanding? I was aware of the 15 digit limits and the binary fraction issue, but, as Harlan stated, there's no apparent reason for Combin to apply the fudge factor in this case. "Biff" wrote: That's the "fudge factor" Harlan was talking about. The result is not EXACTLY zero but the difference is so extremely small that Excel "fudges" the result to be 0. But if A8 = COMBIN(9,3)-84 it appears to properly return 0 Try this: =(COMBIN(9,3)-84)=0 Jerry Lewis has explained that a test for true equality must be done this way. Look for some of his posts where he gets into great detail about this. It's really complex and quite educational. Biff "JMB" wrote in message ... But if A8 = COMBIN(9,3)-84 it appears to properly return 0 and if A9 = MOD(A8,1) it also appears to properly return 0. "Harlan Grove" wrote: Biff wrote... this is still a bug. COMBIN should *only* return integers or error values. I agree and had always assumed as much. This little exercise proves otherwise. =MOD((COMBIN(9,3)-84),1) Returns 1. .... Actually, it returns 0.999999999999986. And don't get me started on MOD. With all boils in need of lancing in Excel, what does Microsoft do? Give it a nose job. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
sound oddity?! | Geoff Cox | Powerpoint | 3 | May 29th, 2006 11:20 AM |
Date Reference Oddity | duncan79 | General Discussion | 2 | May 24th, 2006 05:12 PM |
Access/Sybase oddity | Carol Grismore | General Discussion | 2 | October 13th, 2005 10:05 PM |
Excel2000 ODBC query oddity | Arvi Laanemets | General Discussion | 0 | March 10th, 2005 06:35 AM |
Outlook Oddity | F. Edwin Felty | Installation & Setup | 0 | January 24th, 2005 08:29 PM |