Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Upload your resume securely at https://www.dwsimpson.com to be contacted when our jobs meet your skills and objectives.

#51
07-10-2008, 09:14 PM
 NoName Site Supporter Site Supporter SOA AAA Join Date: Nov 2001 Posts: 7,746

Quote:
 Apparently Excel rounds Cosmetically in a futile attempt to make Binary floating-point appear to be Decimal. Consequently Excel confers supernatural powers upon some (not all) parentheses and induces other inconsistencies.
Kahan is an interesting read - he worked with Intel back in the late 70s when they were developing their floating-point coprocessor, and was involved in getting together the IEEE group that created the floating-point standard - and the fact that =(4/3-1)*3-1 and =((4/3-1)*3-1) display as different results is indeed bizarre. However, I am quite sure this is only a display issue and the internal numbers are in accordance with the standard in either case. People working with floating-point numbers who need to be extremely sure of the accuracy need to be aware of the issues, but I think it is a very rare actuarial project for which double-precision arithmetic is not sufficient.
#52
07-11-2008, 07:38 AM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,643

Quote:
 Originally Posted by Brad Gile I have never understood why anyone would think that a spreadsheet designed for the general public would be, per se, usable for serious scientific investigations.
How about for financial results in the insurance business? FAS97 calculations do not fit my definition of "serious scientific investigations." (I suppose I could be wrong about that, though.)
Quote:
 So, WTF are you using Excel beyond the purposes it was designed for?
I am using it for the purposes it was "designed" for, and it is more than a little bit annoying that cross-footing sums can give different answers depending on the order of the parentheses. I think it was actually better when it didn't try to hide the problem and the user needed to know how to test cross-footing with some permitted difference parameter.

In some ways, the decimal representation of numbers (a la mainframe/Cobol) had its advantanges when doing Dollars and Cents. Otherwise, "totals may not agree - due to rounding."
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.

My latest favorite quotes, updated Nov. 20, 2018.

Spoiler:
I should keep these four permanently.
Quote:
 Originally Posted by rekrap JMO is right
Quote:
 Originally Posted by campbell I agree with JMO.
Quote:
 Originally Posted by Westley And def agree w/ JMO.
Quote:
 Originally Posted by MG This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
 Originally Posted by Dr T Non-Fan Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
 Originally Posted by DoctorNo Depends upon the employer and the situation.
Quote:
 Originally Posted by El Actuario Therapists should ask the right questions, not give the right answers.
Quote:
 Originally Posted by Sredni Vashtar I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.
#53
07-11-2008, 07:44 AM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for duolingo and coursera Favorite beer: Murphy's Irish Stout Posts: 89,832 Blog Entries: 6

Quote:
 Originally Posted by NoName Kahan is an interesting read - he worked with Intel back in the late 70s when they were developing their floating-point coprocessor, and was involved in getting together the IEEE group that created the floating-point standard - and the fact that =(4/3-1)*3-1 and =((4/3-1)*3-1) display as different results is indeed bizarre. However, I am quite sure this is only a display issue and the internal numbers are in accordance with the standard in either case. People working with floating-point numbers who need to be extremely sure of the accuracy need to be aware of the issues, but I think it is a very rare actuarial project for which double-precision arithmetic is not sufficient.
To be sure, that's a high-level weirdness, and it's true that generally we're doing calculations in a non-funky range (as it were). That said, I have actually seen loss of precision of calculation, using regular old double in C++, due to a mismatch of magnitudes of numbers.

The very concrete example was doing a weighted average (xA + yB)/(A+B), where x and y are rather small (and of similar magnitude to each other) and A and B are rather large (and of similar magnitude to each other). We were finding a loss of significant figures because the calculations were being calculated as above. When you multiply and divide numbers of vastly different magnitudes, you start losing sig figs. And this can propogate, of course -- we're doing 50-year runs (at least), and we want to show in-force liabilities running out, not persisting due to weird calculation stuff.

We thought of various fixes, but the one that seems to be the most promising was to calculate p = A / (A + B) and then do the weighted average as xp + y(1-p), which would keep all the numbers closer together in magnitude (p is in a reasonable range, like 20 % - 80%).

Another one that a friend told me about was something like x*y*A, where x & y are both very small and A is very large. x * y could give you underflow, so you change it to x * A * y.

When I did a high school internship with a stats prof, I had to do stuff like this, as he had me program (in Fortran) discrete probabilities, such as the Poisson distribution, and he wanted me to calculate it exactly, not just do various continuous approximations. Thing is, if you try to calculate $\frac{\lambda^k}{k!}e^{-\lambda}$ where k is large and lambda around 10 (say), you can get in trouble really quickly. So I started with e^{\lambda} and did a loop where on each step I multipled by lambda / i, where i was marching down from k to 1. Worked really well. So that's in avoiding an overflow problem.
__________________
It's STUMP

#54
07-11-2008, 09:38 AM
 Renaissance Man Member SOA AAA Join Date: Mar 2004 Studying for FROR Posts: 1,010

The problem of precision goes way beyond Excel, and few people understand floating point calculations well enough to be aware of the issues. If you absolutely have to work with exact precision, then you've no choice but to program it yourself using the GMP library (or similar).

Insofar as Excel's precision, if I am ever concerned about the results, I fire up Mathematica and check the calculations.
#55
07-11-2008, 10:35 AM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,643

OK, I take it back. The problem isn't with floating point. It's with the fact that our money is in decimal fractions. We should go to a binary-type system for amounts less than $1.00. Let's see: Half dollar. . . check Quarter . . . . check Bit * . . . . . . check * As in "two bits" of course. It would be worth$0.125 under the present system. I envision it as being a lot like our current dime, but sufficiently different not to cause the confusion that some dollar coins produce. . .

This new approach gets rid of the annoying pennies. It also does away with nickels - that might not be a bad thing either. Of course the stock market recently went through a huge conversion in the other direction.
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.

My latest favorite quotes, updated Nov. 20, 2018.

Spoiler:
I should keep these four permanently.
Quote:
 Originally Posted by rekrap JMO is right
Quote:
 Originally Posted by campbell I agree with JMO.
Quote:
 Originally Posted by Westley And def agree w/ JMO.
Quote:
 Originally Posted by MG This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
 Originally Posted by Dr T Non-Fan Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
 Originally Posted by DoctorNo Depends upon the employer and the situation.
Quote:
 Originally Posted by El Actuario Therapists should ask the right questions, not give the right answers.
Quote:
 Originally Posted by Sredni Vashtar I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.
#56
07-21-2008, 10:34 AM
 campbell Mary Pat Campbell SOA AAA Join Date: Nov 2003 Location: NY Studying for duolingo and coursera Favorite beer: Murphy's Irish Stout Posts: 89,832 Blog Entries: 6

http://www.cfo.com/article.cfm/11773...todayinfinance
Quote:
 This year's theme, "In Pursuit of Spreadsheet Excellence," placed an emphasis on training and education. To this end, Mbwana Alliy of Microsoft noted that "we're not a training company, we're a software company." He suggested that Excel training be included in the continuing-education requirements for qualified accountants. Later in the day, Patrick O'Beirne of Systems Modelling, an Irish software and consultancy firm, noted that the accountancy body in his home country was considering just such a move. .... Corporate managers are probably more willing to discuss their sex lives than the state of spreadsheet controls within their firms, one of the organisers said.
__________________
It's STUMP

#57
07-21-2008, 10:52 AM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,643

"Corporate managers are probably more willing to discuss their sex lives than the state of spreadsheet controls within their firms, one of the organisers said. "

The people on this board may feel the same way.
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.

My latest favorite quotes, updated Nov. 20, 2018.

Spoiler:
I should keep these four permanently.
Quote:
 Originally Posted by rekrap JMO is right
Quote:
 Originally Posted by campbell I agree with JMO.
Quote:
 Originally Posted by Westley And def agree w/ JMO.
Quote:
 Originally Posted by MG This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
 Originally Posted by Dr T Non-Fan Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
 Originally Posted by DoctorNo Depends upon the employer and the situation.
Quote:
 Originally Posted by El Actuario Therapists should ask the right questions, not give the right answers.
Quote:
 Originally Posted by Sredni Vashtar I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.
#58
07-21-2008, 12:52 PM
 thing Member Join Date: Sep 2001 Location: Oregon Posts: 8,440

__________________
Visit thing's web empire:
Printable Sudoku Puzzles & 12 by 12 Sudoku & 6 by 6 Sudoku & Nurikabe
#59
07-21-2008, 01:03 PM
 E Eddie Smith SOA Join Date: May 2003 College: UGA Posts: 9,352

Quote:
 Originally Posted by thing Ratio of threads discussing sex life to threads discussing spreadsheet controls on this forum (estimated): 78,457:1
Maybe we should ask for a new forum. . . "Sex, Lies, and Spreadsheet Controls"
__________________

Learn how FSA exams are different from the prelims

LPM | LFV-U | LFV-C | LAM | ERM | QFI Quant | QFI PM | QFI IRM | G&H DP | G&H FV | G&H Sp

Check out our Technical Skills Course and our new R Course!

#60
07-21-2008, 01:28 PM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,643

Quote:
 Originally Posted by thing Ratio of threads discussing sex life to threads discussing spreadsheet controls on this forum (estimated): 78,457:1
I fully intended for the ratio to include the Reef*, not just the Technology part of the AO.

* . . . and fantasies, wherever they appear, such is in Cyberchat surveys.
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.

My latest favorite quotes, updated Nov. 20, 2018.

Spoiler:
I should keep these four permanently.
Quote:
 Originally Posted by rekrap JMO is right
Quote:
 Originally Posted by campbell I agree with JMO.
Quote:
 Originally Posted by Westley And def agree w/ JMO.
Quote:
 Originally Posted by MG This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
 Originally Posted by Dr T Non-Fan Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
 Originally Posted by DoctorNo Depends upon the employer and the situation.
Quote:
 Originally Posted by El Actuario Therapists should ask the right questions, not give the right answers.
Quote:
 Originally Posted by Sredni Vashtar I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.