Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

Reply
 
Thread Tools Display Modes
  #41  
Old 06-25-2008, 06:10 PM
Dan Moore's Avatar
Dan Moore Dan Moore is offline
Member
SOA AAA
 
Join Date: Jan 2008
College: University of Dallas
Posts: 1,833
Blog Entries: 1
Default

Quote:
Originally Posted by Expunge View Post
anyways... On Page 9 in talks about common errors, and in the paragraph it says "...as long as you construct the range names automatically." How does one construct range names automatically?
Select the range to be named, and type the name into the drop-down name box just left of the formula bar?
__________________
The best time to plant an oak tree is twenty years ago. The second best time is right now.
Reply With Quote
  #42  
Old 06-25-2008, 08:23 PM
E's Avatar
E E is offline
Eddie Smith
SOA AAA
 
Join Date: May 2003
College: UGA
Posts: 8,759
Default

Using INDEX and MATCH can also alleviate some of the aforementioned problems--although the drawback I've experience with those is a lack of understanding by future users. People seem to "get" VLOOKUP better than INDEX and MATCH functions. For some reason, there is an unwillingness to use the built-in help menu or google. . but that's a separate issue/tirade I could go on, so I'll stop now.
__________________


Learn how FSA exams are different from the prelims

Registration is open for all of our fall 2017 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
  #43  
Old 07-01-2008, 11:32 AM
ACCtuary's Avatar
ACCtuary ACCtuary is offline
Member
SOA
 
Join Date: Oct 2001
Location: Montgomery County, PA (Phila area)
Studying for none
College: MIT
Favorite beer: Sam Adams
Posts: 6,498
Default

Quote:
Originally Posted by no driver View Post
I was unaware of R1C1 style cell references before reading this paper. I'm not sure I get the usefulness of R1C1 over $A$1 style cell references. Can anyone comment from experience? If you use R1C1, do you use it all the time or only in certain circumstances? Do other people using your spreadsheets have a hard time reading/understanding/using your spreadsheets?
I love R1C1 references, particularly when working with large tables. If I want to know the offset from AB45 to U38, I have to slow down my thinking process. If I have R1C1, I just subtract. It just helps me mentally to keep track of my place in the spreadsheet. Relative and absolute references work nicely too. RC1 tells me that column1 is absolute and the row reference is fluid.
__________________
Sticks and Stones may break my bones, but Markov Chains excite me.
Reply With Quote
  #44  
Old 07-03-2008, 02:38 PM
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,010
Blog Entries: 6
Default Three Men in a Bar Found a Spreadsheet Society

http://dobbscodetalk.com/index.php?o...html&Itemid=29

Quote:
"A study by the Computer Audit Unit of HM Customs and Excise also found a relatively low error rate of 11 per cent. But where there were mistakes, the spreadsheets were out by amounts ranging from a few hundred pounds to millions of pounds.

"The exercise was so successful in turning up mistakes in the way companies calculate VAT payments due on their sales that all VAT inspectors are now taught to spot flaws in spreadsheets, either in the way they are set up or through wrong entries. Not all spreadsheet errors are simple mistakes. A spokesman for the Computer Audit Unit says that at least one case analysis of a spreadsheet has produced a conviction for fraud. "

Quote:

I can now claim to be the only Tax Inspector ever to have founded a learned society in order to protect Her Majesty's revenues.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #45  
Old 07-08-2008, 11:31 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,010
Blog Entries: 6
Default

I've got another article out on this subject:
http://soa.org/library/newsletters/c...2008-iss28.pdf

Spreadsheet Check and Control: A Review

Excerpt:
Quote:
In these days of Enron, Sarbanes-Oxley,
and Société Générale, it would be hard to
overemphasize the need to control financial
calculations, especially as they are handled
in spreadsheets. To that end, I could stand
atop a pile of skulls, broadsword in hand,
proclaiming: “Take heed – control your
spreadsheets or suffer DOOM!” That would be
overemphasis…but not by much.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #46  
Old 07-10-2008, 02:47 PM
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,010
Blog Entries: 6
Default

I'm working on an article about (im)precision in floating point arithmetic and came across an excellent webpage from a CompSci prof:

http://www.cs.berkeley.edu/~wkahan/

I've just started to read through his presentations, but there is a =lot= of juicy stuff there, and he shows some errors from Excel in the one I'm reading right now, so I'm going to think about this a little bit.

However, this stuff has showed up a lot in dealing with Excel, and we've had some issues with loss of precision in some of our C++ programs. I think there's not much awareness about this issue (but I could be wrong).
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #47  
Old 07-10-2008, 02:55 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,137
Default

"Exceptions become Errors only when mishandled."

Hear Hear!!

And, "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."

__________________
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 Aug 15, 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 Woodrow View Post
Complementing your technical skills with social skills - whatever they are - will help your career. This is not a sign that the world is unjust.
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 PeppermintPatty View Post
It's SO much better to work for a good manager.
Quote:
Originally Posted by Patience View Post
slow down
Reply With Quote
  #48  
Old 07-10-2008, 04:52 PM
Brad Gile's Avatar
Brad Gile Brad Gile is offline
Member
CAS SOA AAA
 
Join Date: Sep 2001
Studying for whatever I feel like
College: Alumnus of Brown and UW-Madison
Posts: 11,341
Default

Quote:
Originally Posted by JMO View Post
"Exceptions become Errors only when mishandled."

Hear Hear!!

And, "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."

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.

So, WTF are you using Excel beyond the purposes it was designed for?
__________________
Brad Gile, FSA, MAAA
Affiliate Member of the CAS
Dedicated Retired Actuary

Spoiler:
Obama sucks and we all know it-TDA


Spoiler:

That's been the funniest subplot of this whole thing, the people on the left attacking this bill for not being even more of a steaming pile. - erosewater
Reply With Quote
  #49  
Old 07-10-2008, 05:00 PM
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,010
Blog Entries: 6
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.

So, WTF are you using Excel beyond the purposes it was designed for?
Excel is a big calculator. Now, for serious scientific investigations, I agree (they should be using matlab, or R, or other such computational tools which are intended for heavy-duty numerical work). But if you read his stuff, even business calculations get screwed up.

AND many of the problems Excel has with handling floating point is endemic amongst compilers. You get unexpected loss of precision by the very nature of how floating point operates. Again, I don't think many actuaries know about this.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #50  
Old 07-10-2008, 05:05 PM
Oedipus Rex Jr's Avatar
Oedipus Rex Jr Oedipus Rex Jr is offline
Member
 
Join Date: Jun 2008
Posts: 203
Default

I've seen some pretty heavy use of Excel and VBA code for stochastic simulation of various scenarios, so rounding would definitely be an issue there. I would call that "scientific computing", wouldn't you Brad?
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 09:24 PM.


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.33320 seconds with 10 queries