Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
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 new jobs meet your skills and objectives.


Reply
 
Thread Tools Display Modes
  #51  
Old 07-10-2008, 09:14 PM
NoName's Avatar
NoName NoName is offline
Site Supporter
Site Supporter
SOA AAA
 
Join Date: Nov 2001
Posts: 7,735
Default

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.
Reply With Quote
  #52  
Old 07-11-2008, 07:38 AM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 36,312
Default

Quote:
Originally Posted by Brad Gile View Post
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 Oct 13, 2017.

Spoiler:
I should keep these four permanently.
Quote:
Originally Posted by rekrap View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
MORE:
Quote:
Originally Posted by Bro View Post
I recommend you get perspective.
Quote:
Originally Posted by Enough Exams Already View Post
Dude, you can't fail a personality test. It just isn't that kind of test.
Quote:
Originally Posted by Locrian View Post
I'm disappointed I don't get to do both.
Quote:
Originally Posted by elleminopee View Post
For me, I take accusations of simplicity as more of a compliment than an insult. When the level of complexity in your model is more than you can validate, you are wasting your time.
Reply With Quote
  #53  
Old 07-11-2008, 07:44 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 78,966
Blog Entries: 6
Default

Quote:
Originally Posted by NoName View Post
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 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

LinkedIn Profile
Reply With Quote
  #54  
Old 07-11-2008, 09:38 AM
Renaissance Man's Avatar
Renaissance Man Renaissance Man is offline
Member
SOA AAA
 
Join Date: Mar 2004
Studying for FROR
Posts: 1,010
Default

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.
Reply With Quote
  #55  
Old 07-11-2008, 10:35 AM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 36,312
Default

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 Oct 13, 2017.

Spoiler:
I should keep these four permanently.
Quote:
Originally Posted by rekrap View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
MORE:
Quote:
Originally Posted by Bro View Post
I recommend you get perspective.
Quote:
Originally Posted by Enough Exams Already View Post
Dude, you can't fail a personality test. It just isn't that kind of test.
Quote:
Originally Posted by Locrian View Post
I'm disappointed I don't get to do both.
Quote:
Originally Posted by elleminopee View Post
For me, I take accusations of simplicity as more of a compliment than an insult. When the level of complexity in your model is more than you can validate, you are wasting your time.
Reply With Quote
  #56  
Old 07-21-2008, 10:34 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 78,966
Blog Entries: 6
Default

Spreadsheets: Formulas for Success
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

LinkedIn Profile
Reply With Quote
  #57  
Old 07-21-2008, 10:52 AM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 36,312
Default

"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 Oct 13, 2017.

Spoiler:
I should keep these four permanently.
Quote:
Originally Posted by rekrap View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
MORE:
Quote:
Originally Posted by Bro View Post
I recommend you get perspective.
Quote:
Originally Posted by Enough Exams Already View Post
Dude, you can't fail a personality test. It just isn't that kind of test.
Quote:
Originally Posted by Locrian View Post
I'm disappointed I don't get to do both.
Quote:
Originally Posted by elleminopee View Post
For me, I take accusations of simplicity as more of a compliment than an insult. When the level of complexity in your model is more than you can validate, you are wasting your time.
Reply With Quote
  #58  
Old 07-21-2008, 12:52 PM
thing's Avatar
thing thing is offline
Member
 
Join Date: Sep 2001
Location: Oregon
Posts: 8,437
Default

Ratio of threads discussing sex life to threads discussing spreadsheet controls on this forum (estimated): 78,457:1
__________________
Visit thing's web empire:
Printable Sudoku Puzzles & 12 by 12 Sudoku & 6 by 6 Sudoku & Nurikabe
Reply With Quote
  #59  
Old 07-21-2008, 01:03 PM
E's Avatar
E E is offline
Eddie Smith
SOA AAA
 
Join Date: May 2003
College: UGA
Posts: 8,799
Default

Quote:
Originally Posted by thing View Post
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

Registration is open for all of our spring 2018 FSA exam online seminars:

ILA-LP | ILA-LFV-U | ILA-LFV-C | ILA-LRM | QFI Core | QFI Adv | QFI IRM | G&H Core | G&H Adv | G&H Specialty

Check out our Technical Skills Course: Excel, VBA, Access, brand new R material, and more!

Follow us on Twitter, Facebook, and LinkedIn
Reply With Quote
  #60  
Old 07-21-2008, 01:28 PM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 36,312
Default

Quote:
Originally Posted by thing View Post
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 Oct 13, 2017.

Spoiler:
I should keep these four permanently.
Quote:
Originally Posted by rekrap View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
MORE:
Quote:
Originally Posted by Bro View Post
I recommend you get perspective.
Quote:
Originally Posted by Enough Exams Already View Post
Dude, you can't fail a personality test. It just isn't that kind of test.
Quote:
Originally Posted by Locrian View Post
I'm disappointed I don't get to do both.
Quote:
Originally Posted by elleminopee View Post
For me, I take accusations of simplicity as more of a compliment than an insult. When the level of complexity in your model is more than you can validate, you are wasting your time.
Reply With Quote
Reply

Tags
machine learning, predictive analytics, speadsheet error, spreadsheets

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 03:55 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.49117 seconds with 10 queries