Serving the Quantitative Finance Community

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

Excel tricks

April 24th, 2013, 4:53 pm

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;:-)
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Excel tricks

April 24th, 2013, 5:09 pm

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.
 
User avatar
AVt
Posts: 90
Joined: December 29th, 2001, 8:23 pm

Excel tricks

April 24th, 2013, 5:31 pm

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).
 
User avatar
Polter
Posts: 1
Joined: April 29th, 2008, 4:55 pm

Excel tricks

April 24th, 2013, 5:49 pm

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
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

April 25th, 2013, 9:35 am

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.
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Excel tricks

April 25th, 2013, 11:55 am

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.
 
User avatar
Polter
Posts: 1
Joined: April 29th, 2008, 4:55 pm

Excel tricks

April 25th, 2013, 2:04 pm

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
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Excel tricks

April 25th, 2013, 2:17 pm

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.
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

April 25th, 2013, 3:20 pm

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.
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Excel tricks

April 25th, 2013, 4:02 pm

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.
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

April 25th, 2013, 4:12 pm

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.
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Excel tricks

April 25th, 2013, 4:18 pm

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.
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Excel tricks

April 25th, 2013, 5:00 pm

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.
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Excel tricks

May 2nd, 2013, 2:26 pm

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?