Computer Calculator and Microsoft Excel Programming

Question

Why are the computer calculator and Microsoft Excel programmed differently?  Example: If I try to divide 110,534,964,875,444 / 35,184,372,088,832 = 3.14159265358979 in Excel, but in the computer calculator we get 110,534,964,875,444 / 35,184,372,088,832 = 3.14159265358978.

Answer 1

> ... 110,534,964,875,444 / 35,184,372,088,832 = 3.14159265358979

Hi.  Just for a side note, your rational number can actually be reduced..

110534964875444 / 35184372088832

Equals:

 27633741218861 /  8796093022208

Were you trying to find a rational number for Pi?

If interested, the 'smallest' rational number that Excel can calculate as Pi at machine precision would be:

80143857 / 25510582

=EXACT(PI(),  80143857 / 25510582)

True

Answer 2

Michael wrote:

Yes, calculator under accessories, version 6.1

Okay.  My calculator version is 5.1.

In any case, did I answer your question?  If not, what more are you looking for in an answer?

Bear in mind that none of us has knowledge of the internal implementation of these applications.

Answer 3

Yes, calculator under accessories, version 6.1

Answer 4

PS....  I wrote:

In Excel 2010 and WinXP (my versions), the two return exactly the same, namely:

3.1415926535897824578569270670413970947265625.

Excel formats only up to the first 15 significant digits, rounding the 16th.  So we see 3.14159265358978.

But the Excel PI function returns exactly

3.141592653589793115997963468544185161590576171875,

which Excel displays as 3.14159265358979. <wink>

Aside....  We should not use Excel or any (most) computer programmers to try to duplicate or validate mathematical results.

Computer arithmetic differs from mathematics because mathmetical calculations have an unbounded number of digits, whereas computer arithmetic has a limited number of digits, even if it is extensible.  (The amount of memory is finite.)

Answer 5

Michael wrote:

Why are the computer calculator and Microsoft Excel programmed differently?  Example: If I try to divide 110,534,964,875,444 / 35,184,372,088,832 = 3.14159265358979 in Excel, but in the computer calculator we get 110,534,964,875,444 / 35,184,372,088,832 = 3.14159265358978.

What do you mean by "computer calculator"?  If you mean the calculator that we find under Accessories by clicking on Start / All Programs, what version of Windows do you have?

In Excel 2010 and WinXP (my versions), the two return exactly the same, namely:

3.1415926535897824578569270670413970947265625.

[Errata....  I can only assume the Calculator gets the same result tothat degree of precision.]

Excel formats only up to the first 15 significant digits, rounding the 16th.  So we see 3.14159265358978.

Windows Calculator formats up to the first 32 significant digits, rounding the 33nd.  So we see 3.1415926535897824578569270670414.

(I might have changed some Calculator options in order to see so many digits.  I don't remember.)

In any case, infinitesimal differences are certainly possible, especially when there is more than one arithmetic operation.  There can be several reasons.  Among them:

1. Differences in binary representation of stored numbers.  64-bit floating-point is the most common; it is the native binary form for most computers.  But some computers support other binary forms.  And we can always implement different forms in software.
2. Differences in precision of binary computation and intermediate results of subexpressions.  In Intel-compatible CPUs, binary arithmetic is performed using an 80-bit floating-point form; intermediate results in that form are available to software.  That is why Excel and VBA sometimes have different results:  VBA tries to use the 80-bit intermediate results, whereas Excel rounds each subexpression to the 64-bit form.  And again, we can always do things differently in software.