IEE 754 (just curious)
Hans Knudsen
I understand that not all decimal fractions can be presented exactly in
binary and thus why it is that the formula:
=IF(0.5-0.4-0.1=0;"I did not eat Bill's nose","I ate Bill's nose")
returns I ate Bill's nose. However I do not understand why it is that the
formula:
=0.5-0.4-0.1 formatted with maximum number of decimals in Scientific shows
0.000000000000000E+00. Is it because the "error" is less than the minimum
positive number that can be stored?
Hans Knudsen
Bernard Liengme
I get the same result -- "I ate Bill nose"
If I edit the formula and I select the part 0.5-0.4-.01 in the formula bar,
then press F9 to evaluate the expression, it gives me zero. I get 0 if I
use -0.5-0.4-0.1 in a cell all be itselft and format it to 18 places,
scientific notation.
However, if I use XL2002 and use the Evaluate tool on the IF formula, then
the expression comes out as -2.77....E-17. Also get the same result with
=0.5-0.4-0.1=0.
All very strange. The expression evaluates somewhat differently in the IF
compared to being in a cell on its own. I think MS does not fully comply
with the IEEE protcol.
Beranrd
"Hans Knudsen" <hans.knudse...@mail.tele.dk> wrote in message
news:OqDMdcU8...@TK2MSFTNGP11.phx.gbl...
Harlan Grove
...
>If I edit the formula and I select the part 0.5-0.4-.01 in the formula
bar,
>then press F9 to evaluate the expression, it gives me zero. I get 0 if I
>use -0.5-0.4-0.1 in a cell all be itselft and format it to 18 places,
>scientific notation.
>
>However, if I use XL2002 and use the Evaluate tool on the IF formula, then
>the expression comes out as -2.77....E-17. Also get the same result with
>=0.5-0.4-0.1=0.
>
>All very strange. The expression evaluates somewhat differently in the IF
>compared to being in a cell on its own. I think MS does not fully comply
>with the IEEE protcol.
Microsoft not comply with a non-Microsoft standard - there's something
unusual!
My own favorite is =QUOTIENT(-X,Y) where -X < 0, Y > 0, and ABS(X) < ABS(Y).
It gives -0, and -0 <> 0 in Excel, though it damn well is supposed to per
IEEE. Indeed, it must take a considerable amount of work to return -0 at
all, but one can count on Microsoft expending the extra effort to yield
bizarre results in Excel.
Microsoft rounds some but not all arithmetic results to 15 decimal digits.
This is inconsistent in the technical sense. This illustrates why
consistency is essential in arithmetic. This also illustrates the futility
of Microsft trying to round any calculations to 15 decimal digits.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
David J. Braden
Could you please expand on that last paragraph? In particular, what do
you mean by " the futility of Microsft trying to round any calculations
to 15 decimal digits." ???
I'm not out to bust your chops (this time <g>), I just want to
understand what you mean to say. A specific example wouldn't hurt
either.
TIA
Dave Braden
In article <u0LxqwY8...@TK2MSFTNGP10.phx.gbl>,
"Harlan Grove" <hrl...@aol.com> wrote:
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Jerry W. Lewis
returns -2.77555756156289E-17, not zero.
=0.5-0.4-0.1
returns zero because subtraction is the last operation and the two
numbers involved in the subtraction are equal to 15 figurs. In that
combination of factors, MS sets the result to zero, presumably hoping to
avoid questions caused by finite precision binary format.
0.5 is exactly representable in binary. The binary approximations to
0.4 and 0.1 (expresed in decimal) are
0.40000000000000002220446049250313080847263336181640625
0.1000000000000000055511151231257827021181583404541015625
When you sequentially subtract these from 0.5 in Excel, =(0.5-0.4-0.1)
returns
-0.0000000000000000277555756156289
which is correct to the advertized 15 figures.
Jerry
Jerry W. Lewis
subtractions that are the final operation if the two terms are equal to
15 figures. Wrapping the expression in parentheses (or in an IF
function) makes the subtraction not the final operation, so the fuzz
factor is not applied.
Jerry
Jerry W. Lewis
the Pentium chip < sorry David :-) >. Therefore arithmetic issues are
usually issues with Intel (which is supposedly IEEE 754 compliant)
rather than issues with Microsoft.
Excel does do some post-processing of results, which may be the basis of
the inconsistency that you refer to.
- Negative zero: Excel and VBA seem to filter out IEEE -0 results
(many mathematicians question whether the minimal advantage of -0 is
worth the violence it does to basic properties of reals), so that Excel
users are generally unaware of this strange feature of the IEEE 754
standard. The one place this filter fails is in returns from VBA to Excel.
- There is a difference between =a-b and =(a-b) if a<>b but a=b to 15
decimal figures. =a-b will zero the result in a futile attempt to avoid
confusion caused by binary approximations to finite decimal fractions.
Otherwise I am unaware of any automatic rounding of results (beyond what
is inherent in IEEE double precision). Perhaps you could enlighten me?
Jerry
Harlan Grove wrote:
> "Bernard Liengme" <blie...@stfx.ca> wrote...
> ...
David J. Braden
> the Pentium chip < sorry David :-) >.
Low blow. Funny nonetheless. But I'm happy with the Mac's FPU. No
embarassing glitches in the thing, either, as opposed to Intel's fiasco
some years back.
Could you please
(1) Enlighten me re the "advantage" of -0? WHT is it, anyway?
(2) Provide an example where VBA filters out -0, yet returns it to Excel?
(3) Give me a sense of what is actually going on with =a-b versus
=(a-b)? Is it that the latter is working with the full 53 bits, and the
former not?
TIA
Dave Braden
In article <3E7F0390.7070900@no_e-mail.com>,
--
ROT13 e-mail: qoe...@ebpurfgre.ee.pbz
Jerry W. Lewis
http://groups.google.com/groups?selm=3DF5FA27.3080604%40no_e-mail.com
(2) Put 0 in A1, =-A1 in A2 and =A1=A2 in A3. A3 is TRUE.
Consider the following VBA function
Function test()
a = 0
test = -a
If a = test Then
MsgBox ("filtered")
Else
MsgBox ("respected")
End If
End Function
The IEEE difference between a and test is filtered in VBA, but =test()
returns -0 to the worksheet, where it is not equal to 0.
(3) =(a-b) returns exactly what it should in the context of IEEE double
precision. =a-b is identical to =(a-b) EXCEPT when a<>b, yet when
displayed in decimal (15 figurs) you can see no difference in a and b;
in that case =a-b will return zero.
Jerry
David J. Braden wrote:
> ...
Dana DeLouis
is different than most other programs, like Lotus. Do you think this has
anything to do with it?
An old Excel question was on the following idea.
=-(3)^2
Lotus & Mathematica suggest the answer is -9, but Excel returns +9.
XL2000: Tips for Importing Lotus 1-2-3 Files into Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q179841
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"David J. Braden" <dbr...@real.address.below> wrote in message
news:dbraden-FF4A31...@msnews.microsoft.com...
Dana DeLouis
An additional interesting observation is the following
=QUOTIENT(-1,2)-0
=QUOTIENT(-1,2)+0
The first one keeps the -0 number.
The second one changes it to +0.
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Harlan Grove" <hrl...@aol.com> wrote in message
news:u0LxqwY8...@TK2MSFTNGP10.phx.gbl...
<snip>
Harlan Grove
>This might not be related, but I remember that Excel's order of evaluation
>is different than most other programs, like Lotus. Do you think this has
>anything to do with it?
>An old Excel question was on the following idea.
>
>=-(3)^2
>
>Lotus & Mathematica suggest the answer is -9, but Excel returns +9.
Aslo -9 for FORTRAN, Perl, Awk, VB[A] and the spreadsheet in Microsoft
Works. Who needs consistency?
Most programming languages (used here in the broad sense which includes
spreadsheet formulas as a form of functional programming language) give the
exponentiation operator higher precedence than unary minus. Excel is
definitely out of the mainstream in this regard.
Your use of the term 'suggest' is inappropriate. Operator precedence is a
matter of convention, but if you look in any elementary algebra text you'll
see -x^2 invariably treated as -1*x^2, which given even Excel's operator
precedence will return a negative. To me the only annoying aspect of this is
that Excel worksheet formulas and VBA have different operator precedence.
Methinks this was a mistake that, once made, can't be fixed without breaking
existing workbooks - much like Lotus 123's 29-Feb-1900 howler.
It shouldn't have anything to do with the problem discussed elsewhere in
this thread.
Harlan Grove
>Could you please expand on that last paragraph? In particular, what do
>you mean by " the futility of Microsft trying to round any calculations
>to 15 decimal digits." ???
>
>I'm not out to bust your chops (this time <g>), I just want to
>understand what you mean to say. A specific example wouldn't hurt
>either.
Jerry already gave examples. There are times when =a-b in one cell (X) and
=(a-b) in another cell (Y) give different answers, so that =X=Y evaluates to
False. This makes sense?
What I meant to say was that it's futile to do this selectively rather than
ALL THE TIME. Excel doesn't do it all the time, so there are more situations
than there should be when expressions that should be equal aren't.
Jerry W. Lewis
>
> ... Operator precedence is a
> matter of convention
To go off on a completely different tangent, do you know of a freely
available basic package that offers a fairly standard flavor of Basic
yet has no operator precedence (other than parentheses)?
I have a project where I need to provide code that will run under such a
system ( where =a+b*c evaluates as =(a+b)*c ), so VBA is not a reliable
test platform.
Jerry
Hans Knudsen
I do not fully understand all of it :-)).
By the way - I wonder if what has been mentioned here also explain the
results of the following formulas:
=FALSE=1-TRUE (returns FALSE) while
=FALSE*1=1-TRUE (returns TRUE)
Hans Knudsen
"Hans Knudsen" <hans.knudse...@mail.tele.dk> skrev i en meddelelse
news:OqDMdcU8...@TK2MSFTNGP11.phx.gbl...
Jerry W. Lewis
> Thanks to all who responded regarding IEEE 754 (even if I have to admit that
> I do not fully understand all of it :-)).
You're welcome.
> By the way - I wonder if what has been mentioned here also explain the
> results of the following formulas:
>
> =FALSE=1-TRUE (returns FALSE) while
> =FALSE*1=1-TRUE (returns TRUE)
No. Deconstruct your equation. Following Excel's rules of
operator precedence =FALSE*1=1-TRUE is equivalent to
=(FALSE*1)=(1-TRUE). Now consider how each of the pieces in parentheses
evaluates:
=1-TRUE returns zero, but =FALSE=0 returns FALSE (similarly =1*TRUE
returns 1, but =TRUE=1 returns FALSE)
=FALSE*1 returns zero and =0=0 returns TRUE.
The net result is that while TRUE behaves like 1 and and FALSE behaves
like 0 when you do arithmetic with them, Excel still recognizes that the
boolean values themselves are booleans and not numbers.
Jerry
Harlan Grove
...
>To go off on a completely different tangent, do you know of a freely
>available basic package that offers a fairly standard flavor of Basic
>yet has no operator precedence (other than parentheses)?
>
>I have a project where I need to provide code that will run under such a
>system ( where =a+b*c evaluates as =(a+b)*c ), so VBA is not a reliable
>test platform.
AFAIK, APL (and its descendents) is the only language with infix operators
that has no operator precedence. I just did a web search that turned up two
dialects called Tiny Basic and PBASIC, but the former appears to provide
only integer type. I didn't dig into the latter. You may be reduced to
having to roll your own, though strict left to right evealuation order
should be simple to implement.
Dana DeLouis
quick and dirty. I'm sure it doesn't cover everything. Hopefully you can
adjust it to fit your needs...
Sub TestIt()
' Normally, answer is 38
Debug.Print CalculateLeft2Right("3 + 5 * 7") ' 56
Debug.Print CalculateLeft2Right("3 + (5 * 7)") ' Still 56
'
Debug.Print CalculateLeft2Right("3 + 12/6") ' 2.5 instead of 5
End Sub
Function CalculateLeft2Right(Equ As String) As Double
' Dana DeLouis
Dim j As Long
Dim t As Variant
Dim NormalAnswer
Dim s As String
NormalAnswer = Evaluate(Equ)
' Remove '(' & ')', since they are not used
s = Replace(Equ, "(", vbNullString)
s = Replace(s, ")", vbNullString)
For j = 1 To Len(s)
Select Case Mid$(s, j, 1)
Case "+", "-", "*", "/"
t = Evaluate(Left$(s, j - 1))
s = t & Mid$(s, j)
End Select
Next j
CalculateLeft2Right = Evaluate(s)
End Function
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3E82F02B.3090801@no_e-mail.com...