Limits and Accuracy in Excel and Maple: A Comparative Study, Lab Reports of Materials science

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-308 2.225073858507200000E-308 0.000000000000000000E+00 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+307 4.494232837155790000E+307 8.988465674311580000E+307 #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 101292913986 under := 3.112193347 10-1292913987 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 1 ...Integer too large for display... The last correct result is at i=21 and is several pages long. The numbers are approximately equal to 10362880 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. 362880 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 The “optimum” number of subintervals (minimum error before roundoff error hits) is shown below: y = 1.8533x0.1546 R2 = 0.9485 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, (r el at iv e er ro r i s 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 2000*sin(2000.5*x) from 0 to Pi is shown below, and is quite large!... (can you tell why?) 1% 10% 100% 1000% 10000% 100000% 1000000% 1 10 100 1000 10000 100000 1000000 1000000 0 N, number of intervals Re la tiv e er ro r ( % ) Figure 3. “Optimum” number of subintervals for the integration of 2000*sin(2000.5*x)from 0 to pi by trapezoid rule. Improvements of the trapezoid rule: Use variable size subintervals by performing an “adaptive” integration, i.e. for each subinterval compare the value of the area with the corresponding area calculated by subdividing the interval into two. Continue until the difference between the two areas is smaller than a tolerance ε. Figure 4. Adaptive trapezoid rule.