• 1
• 4
• 5
• 6
• 7
• 8 Polter
Posts: 2526
Joined: April 29th, 2008, 4:55 pm

### Excel tricks

edouard: it's not about Excel v. MATLAB v. Wolfram Alpha, etc.It's about floating-point arithmetic v. arbitrary-precision arithmetic v. fixed-point arithmetic.For instance, using fixed-point (100) decimal type:http://coliru.stacked-crooked.com/view? ... nvestigate what happens when you changetypedef boost::multiprecision::number<cpp_dec_float<100>> ScalarType;totypedef boost::multiprecision::number<cpp_dec_float<1000>> ScalarType;:-) Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel tricks

QuoteOriginally posted by: Polteredouard: it's not about Excel v. MATLAB v. Wolfram Alpha, etc.It's about floating-point arithmetic v. arbitrary-precision arithmetic v. fixed-point arithmetic.For instance, using fixed-point (100) decimal type:http://coliru.stacked-crooked.com/view? ... nvestigate what happens when you changetypedef boost::multiprecision::number<cpp_dec_float<100>> ScalarType;totypedef boost::multiprecision::number<cpp_dec_float<1000>> ScalarType;:-)Exactly! And it's worse than that because both Wolfram Alpha and some compilers might perform analytical/algebraic/logic manipulations of a formula prior to numerical evaluation. I'd not be surprised if putting "f(x) == g(y)" into Wolfram Alpha triggers a theorem-prover like process rather than simple numerical evaluation of f(x), then g(y), with a test for numerical equivalence. It doesn't take much to show that the log of a ratio of two numbers is the difference in the logs of those two numbers and conclude that the equality is TRUE regardless of precision or round-off errors in the numerical log function.The point is that you might get different round-off errors for the identical code, identical inputs, identical processor, but different compilers.
Last edited by Traden4Alpha on April 23rd, 2013, 10:00 pm, edited 1 time in total. AVt
Posts: 1074
Joined: December 29th, 2001, 8:23 pm

### Excel tricks

Note that Excel displays only 15 Digits rounded and appends zeros, if you ask for more.Actually Excel uses IEEE, even if MS does not guarantee it (well C++ does not as well).And in IEEE that results are "correct" - up to the last ULP, you can try it using a smallC program.Also note that Excel does not use the 3200/2345 as input, but the according float. And for that MMA should return false, of course.Otherwise it is asked to check for functionality of log, not for numerical identity(which roughly never makes sense). Polter
Posts: 2526
Joined: April 29th, 2008, 4:55 pm

### Excel tricks

Exercise for edouard -- explain the results:log(3200.0/2345.0) - (log(3200.0)-log(2345.0)) http://www.wolframalpha.com/share/clip? ... 10.0^16.0) * (log(3200.0/2345.0) - (log(3200.0)-log(2345.0))) http://www.wolframalpha.com/share/clip? ... 55os1omdcn Cuchulainn
Posts: 62371
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

These are very depressing posts. At application level we need to be aware of these rounding errors, everywhere and always.
Last edited by Cuchulainn on April 24th, 2013, 10:00 pm, edited 1 time in total. Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel tricks

QuoteOriginally posted by: CuchulainnThese are very depressing posts. At application level we need to be aware of these rounding errors, everywhere and always.We'd need to handle this anyway. Isn't measurement error in most data much worse than round-off error in floats and doubles?The only added concern come from the extreme cardinalities of numerical elements in big-data and HPC sims. If one has 10^3 data elements, it's not likely that a 1-part-in-10^15 round-off error will be a problem. But if one has 10^12 elements, it's a bigger issue. Polter
Posts: 2526
Joined: April 29th, 2008, 4:55 pm

### Excel tricks

QuoteOriginally posted by: Traden4AlphaIsn't measurement error in most data much worse than round-off error in floats and doubles?And let's not forget modeling error and approximation error:http://www.johndcook.com/blog/2011/11/0 ... eProfessor Nick Trefethenif computers were suddenly able to do arithmetic with perfect accuracy, 90% of numerical analysis would remain important Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel tricks

QuoteOriginally posted by: PolterQuoteOriginally posted by: Traden4AlphaIsn't measurement error in most data much worse than round-off error in floats and doubles?And let's not forget modeling error and approximation error:http://www.johndcook.com/blog/2011/11/0 ... eProfessor Nick Trefethenif computers were suddenly able to do arithmetic with perfect accuracy, 90% of numerical analysis would remain importantSo true!One might say that floating-point round-off error is a kind of model error. The "real" system has Real numbers but the model uses floats, doubles, etc. Cuchulainn
Posts: 62371
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

QuoteOriginally posted by: Traden4AlphaQuoteOriginally posted by: PolterQuoteOriginally posted by: Traden4AlphaIsn't measurement error in most data much worse than round-off error in floats and doubles?And let's not forget modeling error and approximation error:http://www.johndcook.com/blog/2011/11/0 ... eProfessor Nick Trefethenif computers were suddenly able to do arithmetic with perfect accuracy, 90% of numerical analysis would remain importantSo true!One might say that floating-point round-off error is a kind of model error. The "real" system has Real numbers but the model uses floats, doubles, etc.Not sure if I would agree. I think it fits into category (B) here below.Errors in given input data (D)Round-off errors during computation (B)Truncation errors Simplification of the mathematical model (A)Human and machine errors (C) What I find tricky is to determine if it is A or B. In the past -when computers ran on steam - backward error analysis was part of 1st year numerical analysis courses ( analysis just tells us how big the error is. I would consider undocumented code as an error in class C. Another scenario is the model (A) cannot handle the range of values in (D). We might jump to the conclusion that it is caused by (B).
Last edited by Cuchulainn on April 24th, 2013, 10:00 pm, edited 1 time in total. Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel tricks

QuoteOriginally posted by: CuchulainnQuoteOriginally posted by: Traden4AlphaQuoteOriginally posted by: PolterQuoteOriginally posted by: Traden4AlphaIsn't measurement error in most data much worse than round-off error in floats and doubles?And let's not forget modeling error and approximation error:http://www.johndcook.com/blog/2011/11/0 ... eProfessor Nick Trefethenif computers were suddenly able to do arithmetic with perfect accuracy, 90% of numerical analysis would remain importantSo true!One might say that floating-point round-off error is a kind of model error. The "real" system has Real numbers but the model uses floats, doubles, etc.Not sure if I would agree. I think it fits into category (B) here below.Errors in given input data (D)Round-off errors during computation (B)Truncation errors Simplification of the mathematical model (A)Human and machine errors (C) What I find tricky is to determine if it is A or B. In the past -when computers ran on steam - backward error analysis was part of 1st year numerical analysis courses ( analysis just tells us how big the error is. I would consider undocumented code as an error in class C. Another scenario is the model (A) cannot handle the range of values in (D). We might jump to the conclusion that it is caused by (B).You may be right or it may be a fuzzy line.To me, the analytic model is an approximation of the real system and the numerical/computational model is an approximation of the analytical model. The numerical/computational model contains model errors due to assuming that floats are Real numbers just as the analytic model assumes that the analytic variables accurate reflect the real system.But I do agree that round-off error has a different character than some other modeling errors. Cuchulainn
Posts: 62371
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

QuoteTo me, the analytic model is an approximation of the real system and the numerical/computational model is an approximation of the analytical model. The numerical/computational model contains model errors due to assuming that floats are Real numbers just as the analytic model assumes that the analytic variables accurate reflect the real system.I agree. A sensorUnit can compute an analytical approximation to the real ambient temperature. A software agent may also change its mind. Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel tricks

QuoteOriginally posted by: CuchulainnQuoteTo me, the analytic model is an approximation of the real system and the numerical/computational model is an approximation of the analytical model. The numerical/computational model contains model errors due to assuming that floats are Real numbers just as the analytic model assumes that the analytic variables accurate reflect the real system.I agree. A sensorUnit can compute an analytical approximation to the real ambient temperature. A software agent may also change its mind.And I agree with you because the round-off error has a perverse quality unlike many other types of errors.Most categories of errors decrease with increasing numbers of measurements, equation terms, grid elements, iterations, etc. Fine grain -> lower error. But round-off can go the other way. Cuchulainn
Posts: 62371
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

### Excel tricks

QuoteOriginally posted by: Traden4AlphaQuoteOriginally posted by: CuchulainnQuoteTo me, the analytic model is an approximation of the real system and the numerical/computational model is an approximation of the analytical model. The numerical/computational model contains model errors due to assuming that floats are Real numbers just as the analytic model assumes that the analytic variables accurate reflect the real system.I agree. A sensorUnit can compute an analytical approximation to the real ambient temperature. A software agent may also change its mind.And I agree with you because the round-off error has a perverse quality unlike many other types of errors.Most categories of errors decrease with increasing numbers of measurements, equation terms, grid elements, iterations, etc. Fine grain -> lower error. But round-off can go the other way.Most.Richardson's hand-computed FD scheme in 1911 (by a room full of 'computers') seemed stable until it was put on a computer in 1944? and it was numerically unstable. We can get accumulation of errors that you only see when NX --> big.
Last edited by Cuchulainn on April 24th, 2013, 10:00 pm, edited 1 time in total. Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel tricks

QuoteOriginally posted by: outrunI just found out about the BAHTTEXT() function. What's going on?? Who did that?Someone just Thai-ing up loose ends?  