XNUMBERS - Multi Precision Floating Point Calculus For EXCEL
Multi-Precision Computing.
Xnumbers
is an Excel addin (xla) that performs multi-precision floating point
arithmetic. Perhaps the first package that extends the standard Excel precision
from 1 up to 250 significant digits. It is compatible with Windows Excel
2000/XP (*) and consists of a set of more than 300 functions for arithmetic,
complex , trigonometric, logarithmic, exponential
calculus. Linear Algebra: System resolution with Gauss-Jordan algorithm
(also in a didactic step-by-step method). Gauss-Seidel iterative method. Matrix
operations. Determinant. Matrix Inversion. Characteristic Polynomial with Newton-Girard
formulas. Crout's algorithm for LU decomposition. Similarity Transformation.
Matrix power.
Polynomials: symbolic computation to add, multiply, divide polynomials.
Derivatives of polynomials. Interpolation with
All
documentation on-line. No installation. Freeware. Open Source.
String extended
number
Packet extended number
Using Xnumbers
Precision
Format
Installation Note
Why using extended precision numbers?
Several methods exist for simulating
variable multi-precision floating point arithmetic. The basic concept consists of
breaking down a long number into two or more sub-numbers, and repeating cyclic
operations with them. The ways in which long numbers are stored vary from one
method to another. The two most popular methods use the "string"
conversion and the "packing"
String Extended Numbers
In this method, long numbers are stored as vectors of characters, each
representing a digit in base 256. Input numbers are converted from decimal to
256 base and vice versa for output. All internal computations are in 256 base.
this requires only 16 bit for storing and a 32 bit accumulator for computing.
Here is an example of how to convert the number 456789 into string
String= chr(6)&chr(248)&chr(85)
This method is very fast, and
efficient algorithms for the input-output conversion have been realized. A good
explanation of this method can be found in "NUMERICAL RECIPES in C - The
Art of Scientific Computing", Cambridge University Press, 1992, pp. 920-
Perhaps the most critical factor of this method is the
debugging and test activity. It will be true that the computer does not care
obout the base representation of numbers, but programmers usually do it. During
debugging, programmers examine lots and lots of intermediate results, and they
must always translate them from base 256 to 10. For this kind of programs, the
debugging and tuning activity usually takes 80 - 90% of the total develop time
Packet Extended Numbers
This method avoids converting the
base representation of long numbers and stores them as vectors of integers.
This is adopted in all FORTRAN77 routines of
"MPFUN: A MULTIPLE PRECISION FLOATING POINT COMPUTATION
PACKAGE" by
Of course this add-in does not have
the performance of the mainframe package (16 million digits) but the method is
substantially the same. Long numbers are divided into packets of 6 or 7 digits.
For example, the number 601105112456789 in packet form of 6 digits becomes the
following integer vector:
|
456789 |
|
105112 |
|
601 |
As we can see, the sub-packet
numbers are in decimal base and the original long number is perfectly recognizable.
This a great advantage for the future debugging operation of algorithms.
An example of arithmetic operation -
the multiplication A x B = C - between two packet numbers is shown in the
following
|
A |
|
B |
|
456789 |
|
654321 |
|
105112 |
X |
|
|
601 |
|
|
The schema below illustrates the
algorithm adopted:
|
carry |
|
A |
|
B |
|
C' |
|
C |
|
0 |
+ |
456789 |
x |
654321 |
= |
298886635269 |
=> |
635269 |
|
298886 |
+ |
105112 |
x |
654321 |
= |
68777287838 |
=> |
287838 |
|
68777 |
+ |
601 |
x |
654321 |
= |
393315698 |
=> |
315698 |
|
393 |
+ |
0 |
x |
654321 |
= |
393 |
=> |
393 |
The numbers in the accumulator C'
are split into two numbers. The last 6 digits are stored in C; the remaining left
digits are copied into the carry register of the next computation row
As we can see, the maximum number of
digits is reached in accumulator C'; in the other vectors, the numbers require
only six digits at most. The maximum number of digits for a single packet
depends of the hardware accumulator. Normally, for a 32-system, is 6 digits..
This is equivalent to conversion from a decimal to a 10^6 representation base.
This value is not critical at all. Values from 4 to 7 affect the computation
speed by only about 30 %. But it does not affect the precision of the results
in any case.
Xnumbers is an Excel addin
that performs multi-precision floating point arithmetic from 1 up to 250
significant digits. It consists of a set of more than 220 functions for
arithmetic, trigonometric, logarithmic, exponential and matrix calculus.
The basic arithmetical functions: addition, multiplication, and division
were developed first. They form the basic kernel for all other functions.
All functions perform multiprecision floating point computations for up
to 250 significant digits. You can set a precision level separately for each
function by an optional parameter. By
default, all functions use the precision of 30 digits, but the numerical
precision can easily be regulated continually from 1 to 250 significant digits.
In advance some useful constants like PI, Ln(2), Ln(10) are provided with up to
400 digits.
For Linear Algebra the main topics are: System resolution with
Gauss-Jordan algorithm, Matrix Addition and Multiplication, Determinant, Matrix
Inversion, Crout's algorithm for LU decomposition, LL Cholesk decomposition,
Similarity Transform, Matrix power.
For this addin, multiprecision numbers are strings
containing:
• Up to 250 mantissa
digits.
• Sign character
"+" or "-"
• Decimal symbol (default
".")
• Exponent symbol
"E" or "e"
• Exponent sign character
"+" or "-"
• Up to 10 exponent digits
for a total length of 214 characters.
The functions recognize both decimal format (12.34 , -0.056, etc.) and
exponential format (1.234567E-015). In addition, the functions accept decimal,
double, currency or any other standard Excel number type, or even mixed
(extended + normal) numbers.
Use
These functions can be used in an
Excel worksheet as any other built-in function. After the installation, look up
in the functions library <Functions…> from the menu <Insert>, or
click on the icon
Upon "user's" category you
should find all the functions of this package.
From 2.0 version you can manage
functions also by the Function Handbook, starting by the icon
All the functions for
multi-precision computation begin with "x". The example below shows
two basic functions for addition and subtraction operations.
Like any other functions, they can
also be nested to build complex expression. In the example below we compute x^4
with a precision of 30 digit
You can also insert extended numbers
directly in the function. Only remember that for preserving Excel to convert
them, you must insert them like string, within quotation marks. Ex:
"134.3915574"
xpow("0.39155749636098981077147016011";90)
= 1.9904508921478176508981155284E-7
NB. If you want to insert a long
number in a cell and you do not want Excel to automatic convert it, prefix it
with the hyphen symbol < ' >.
The most part of the functions of
this package have an optional parameter - Digit_Max - setting the max of
significant digits for floating point computation. In this version the maximum
precision level has been set to 250 digits. The default is 30. This parameter
also determines how the output is automatically formatted. If the result has
less digits than Digit_Max, then the output is in the plain decimal format (
123.45, -0.0002364, 4000, etc.); if the
result exceeds the number of digits allowed (significant or not), the output is
automatically converted in exponential format (1.23456789E+94).
From version 2.3 the exponent can
reach the extreme values of +/- 2,147,483,.647.
The output format is independent of
input format.
In synthesis, the Digit_Max
parameter limits:
·
the maximum
number of significant digits of internal floating point computation
·
the maximum
number of digits to output, significant or not.
User cannot format an extended
number with the standard Excel format tools, because the x-numbers are simply
strings of characters for Excel. You can only hange the alignment using the standard Excel format tools.
It is possible to separate the
digits of a string numbers in group, by
the function xFormat().
It works similar to the built-in function
Format(x,
"#,##0.00")
This function was original developed
by Ton Jeursen for his add-in XNUMBER95, the downgrade version of XNUMBERS for
Excel 5. Because it works well and it is very useful for examining long string
of number, I have imported it in this package.
Why
using extended precision numbers?
First of all, for example, to
compute the following operation:
|
90000000002341 |
x |
|
8067 |
= |
|
----------------- |
|
|
726030000018884847 |
|
Any student, with a little work, can
do it. Excel, as any 32-bit machine, cannot! It always gives the (approximate)
result 726030000018885000 , with a difference
of +153.
But
do not ask Excel the difference. It replies 0!
The
second example regards numeric analysis. Suppose we have to find the roots of a
9° degree Polynomial.
Its
coefficients ai are in the table below.
|
Coefficients |
|
|
a9 |
1 |
|
a8 |
-279 |
|
a7 |
34606 |
|
a6 |
-2504614 |
|
a5 |
116565491 |
|
a4 |
-3617705301 |
|
a3 |
74873877954 |
|
a2 |
-996476661206 |
|
a1 |
7738306354988 |
|
a0 |
-26715751812360 |
There are excellent algorithms to find
numerical solution of this problem. We can use the Newton-Raphson method,
starting from x= 32 and operating with 15 significant digits (the maximum for
Excel), we have:
|
xn |
P(x) (15
digit) |
P' (x) (15
digit) |
-P/P' |
|xn-x| |
|
32 |
120 |
428 |
0,280373832 |
1 |
|
31,71962617 |
43,77734375 |
158,9873047 |
0,275351191 |
0,7196262 |
|
31,44427498 |
15,69921875 |
60,93164063 |
0,257652979 |
0,444275 |
|
31,186622 |
4,78125 |
29,46289063 |
0,162280411 |
0,186622 |
|
31,02434159 |
0,65625 |
24,10644531 |
0,02722301 |
0,0243416 |
|
30,99711858 |
-0,07421875 |
24,01953125 |
-0,003089933 |
0,0028814 |
|
31,00020851 |
0,23828125 |
24,04980469 |
0,009907825 |
0,0002085 |
|
30,99030069 |
-0,52734375 |
23,98925781 |
-0,021982495 |
0,0096993 |
|
31,01228318 |
0,2421875 |
24,02050781 |
0,01008253 |
0,0122832 |
|
31,00220065 |
-0,03515625 |
23,99023438 |
-0,00146544 |
0,0022007 |
As we can see, the approcaches
around the solution x = 31 but the error |Xn - X| remains too high. Why?
Multiple roots? No, because P'(x)>>0. Algorithm failed? Of course, not.
This method is very well tested. The only explanation is the finite precision
of the computation. In fact, repeating the calculus of P(x) and P'(x) with 25
significant digits, we find the excellent convergence of this method.
|
xn |
P(x) (25
digit) |
P' (x) (25
digit) |
-P/P' |
|xn-x| |
|
32 |
120 |
428 |
0,28037383 |
1 |
|
31,71962617 |
43,71020049043 |
158,979858019937 |
0,27494175 |
0,719626 |
|
31,44468442 |
15,71277333004 |
61,059647049872 |
0,25733482 |
0,444684 |
|
31,1873496 |
4,83334748037 |
29,483621556222 |
0,1639333 |
0,18735 |
|
31,02341629 |
0,56263326884 |
24,082301045236 |
0,02336294 |
0,023416 |
|
31,00005336 |
0,00128056327 |
24,000000427051 |
5,3357E-05 |
5,34E-05 |
|
31 |
0,00000000053 |
23,999999999984 |
2,2083E-11 |
1,54E-11 |
|
31 |
0,00000000004 |
23,999999999995 |
1,6667E-12 |
6,66E-12 |
The graph below summarizes the effect of this computation with 15 and 25
significant digits.
.
The application field of
multi-precision computation is wide. Overall it is very useful during the
testing of numeric algorithms. In the above example, we had not doubt about the
Newton-Raphson method, but what about the new algorithm that you are studying?
This package helps you in this work.
So long.
Have fun with XNUMBERS !