Download Limits and Accuracy in Excel and Maple: A Comparative Study and more Lab Reports Materials science in PDF only on Docsity!
SAMPLE LAB – 1 REPORT:
PROBLEM 1: Minimum and Maximum number that the “computer” can handle
EXCEL
In a column, each cell is set to be half of the one above it and the column ends as follows:
… 4.450147717014400000E- 2.225073858507200000E- 0.000000000000000000E+
After 2.22507E-308 the spreadsheet considers the number as zero.
By multiplying by two, instead of dividing the following limit is obtained:
2.247116418577890000E+ 4.494232837155790000E+ 8.988465674311580000E+ #NUM!
By dividing the last number by a number closer to 1, a more tight bound can be found: 1.797693134862310000E+308. The same can be done with the lower limit.
The importance of these limits is that if numbers within calculations reach these limits unwanted results can occur, such as division by zero etc.
MAPLE
Implementation of the same procedure in Maple was done via the following algorithm:
> over:=2.; under:=0.5; for i from 1 to 50 do over:=evalf(over)^2; under:=evalf(under)^2; od;
which produced a long list of numbers which ended with: ….
over := 3.083198569 10^1292913986
under := 3.112193347 10-
over := Float ( N )
under := 0.
A quick comparison of the limits in Maple versus the corresponding ones in EXCEL shows that they are significantly different. The reason for this is that MAPLE is programmed to perform calculations in a way that provided better, and user-controllable accuracy that other common programs. Some other peculiarities were also noted. For example if the following code is used:
over:=2; under:=1/2; for i from 1 to 50 do over:=eval(over)^2; under:=eval(under)^2; od;
the program attempts to complete the operations using integers and eliminates round off error. When the limit is reached the output indicates that:
over := ...Integer too large for display...
under :=
...Integer too large for display...
The last correct result is at i=21 and is several pages long. The numbers are approximately equal to 10 362880 and 10 -362880^ for overflow and underflow respectively. Note that this limit is much smaller than were real numbers were used in the sample program.
PROBLEM 2: ACCURACY
EXCEL
The implementation of the suggested algorithm is shown below:
F G H =1/2 =F2+1 =IF(G2=1,"yes","no") =F2/2 =F3+1 =IF(G3=1,"yes","no") =F3/2 =F4+1 =IF(G4=1,"yes","no") … … …
0.5 1.5000000000000000 no 0.25 1.2500000000000000 no 0.125 1.1250000000000000 no … 1.42109E-14 1.0000000000000100 no 7.10543E-15 1.0000000000000100 no
for i from 1 to N do Sum1:=evalf(Sum1+sin(idx)dx); od; print(N, abs(Sum1-INT1)); od:**
The relative error defined as (Calculated – True) / True is plotted in the Figure below versus the number of subintervals, which the integration range is divided into. Results are shown for Maple with Digits 5 and 10 and for FORTRAN with SINGLE and DOUBLE PRECISION. Two regions of performance can be defined. Initially (for small number of subintervals) The relative error scales with the inverse square of the number of subintervals (slope of -2 in log-log plot). This is the expected textbook behavior that says that the error in the integration is reduced as the number of subintervals is larger because the approximation follows the variation of the true function more faithfully.
At some point, however, the performance begins to degrade. As the number of subintervals increases, round-off error accumulates and makes the calculation inaccurate. In fact the larger the number of steps the worse the result.
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E-
1.E+
1.E+
1.E+00 1.E+02 1.E+04 1.E+06 1.E+
Number of subintervals
Relative Error (Calc-True)/True
Maple-Digits= Maple-Digits= FORT RAN-Single Precision FORT RAN-Double Precision
Figure 1. Error in trapezoid rule versus number of subintervals.
The “optimum” number of subintervals (minimum error before roundoff error hits) is shown below:
y = 1.8533x 0. R^2 = 0.
0
1
2
3
4
5
6
7
8
9
10
0 5000 10000 15000 20000 25000 30000 35000 N (number of sub-intervals)
x, (relative error is 10
-x )
Figure 2. “Optimum” number of subintervals for the integration of sin(x) from 0 to pi by trapezoid rule.
The result above for the ”optimum” N can not be generalized because it depends on the specific function to be integrated. For example the error of the trapezoid rule for 2000sin(2000.5x) from 0 to Pi is shown below, and is quite large!... (can you tell why?)
