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

Salary Surveys
Property & Casualty, Life, Health & Pension

Health Actuary Jobs
Insurance & Consulting jobs for Students, Associates & Fellows

Actuarial Recruitment
Visit DW Simpson's website for more info.
www.dwsimpson.com/about

Casualty Jobs
Property & Casualty jobs for Students, Associates & Fellows


Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2007, 01:28 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: 79,369
Blog Entries: 6
Default "How do you know your spreadsheet is right?"

"How do you know your spreadsheet is right?" (PDF) is an excellent article from Philip Bewig, from July 2005.

It mainly deals with Excel and some of its features that can help make your spreadsheets more robust. He goes through various practices that may reduce your error rate in developing complicated spreadsheets (also to prevent some deliberate errors, aka fraud, from being undetectable). I've used some of these techniques myself, but some I had never heard or thought of. Most useful of all is his extensive footnotes that provide jumping off points into particular issues. Lots and lots of links.



A few other useful links:
European spreadsheet risks interest group: http://www.eusprig.org/

Ray Panko’s spreadsheet research pages:http://panko.cba.hawaii.edu/SSR/index.htm
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #2  
Old 04-10-2007, 02:47 PM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,684
Blog Entries: 2
Default

Very interesting. I actually have a meeting in 15 minutes to discuss with some Quality Assurance people how they should go about testing that my SAS program is accurate and does what its supposed to do. My solution for them is an Excel spreadsheet that mimics my SAS code. They really have no way of knowing if I made the same mistake in both places, but by recreating all the calculations in Excel, I found a couple mistakes in the SAS code.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #3  
Old 04-10-2007, 04:41 PM
no driver's Avatar
no driver no driver is offline
Note Contributor
SOA
 
Join Date: Jan 2006
Studying for nothing!
Posts: 2,350
Default

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?
Reply With Quote
  #4  
Old 04-10-2007, 04:55 PM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,684
Blog Entries: 2
Default

What cell is 10 columns to the right of cell T23? Let's see - U,V,W,X,Y,Z,AA,AB,AC,AD...um, is that 10? No? Let me count to make sure....

What cell is 10 columns to the right of cell R23C20? Its R23C30, of course!

Now do you see its usefullness?
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #5  
Old 04-10-2007, 05:10 PM
tommie frazier tommie frazier is offline
Member
 
Join Date: Aug 2003
Favorite beer: The kind with 2 e's
Posts: 22,394
Default

i see it as useful, sure. not what I learned on, so that would take getting used to.
Reply With Quote
  #6  
Old 04-10-2007, 06:07 PM
Phil's Avatar
Phil Phil is offline
Site Supporter
Site Supporter
 
Join Date: Sep 2001
Location: not on board the Enterprise
Posts: 5,667
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. ...
I've also heard that if you convert a whole column to R1C1 display and press Ctrl-' to make Excel display formulas, then everything in that column should be identical and you can easily notice inconsistencies. For example, if reading down Column C, it reads

=A1+B1+D1*F1
=A2+B2+D2*F2
=A3+B3+D3*F3
=A4+B4+D3*F4
then you might not notice that cell # 4 is inconsistent

But in R1C1 format they will all read
=R[-]1[0L[1[-1][1]l]1[-1]0]C]!1[+1[[-1]-1

and then it's easy to find the one that's different.
Reply With Quote
  #7  
Old 04-11-2007, 11:26 AM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,240
Default

Quote:
Originally Posted by Phil View Post
I've also heard that if you convert a whole column to R1C1 display and press Ctrl-' to make Excel display formulas
I don't know if this is a font thing, but it looks like you said ctrl-' (single-quote) when really the display formula command is ctrl-` (the key with the tilde on it - above the tab key. ) This is an awesome formula auditing tool and using it the way that you described (with r1c1) has helped me spot mistakes before.

also, r1c1 formulas are a lot easier to work with in vba (a lot of the time). you don't need to use the r1c1 format in excel to use them, but when you're trying to set up your macro, it's a lot easier to create the formula in excel, then copy it over to VBA and edit it than it is to write it from scratch in the vba editor - that way you can be sure that your syntax is clean (same # of paranthesis and such).
Reply With Quote
  #8  
Old 04-11-2007, 11:37 AM
Phil's Avatar
Phil Phil is offline
Site Supporter
Site Supporter
 
Join Date: Sep 2001
Location: not on board the Enterprise
Posts: 5,667
Default

Whoops, I did say ' and apparently it should be `. You can tell I never use this.
Reply With Quote
  #9  
Old 04-11-2007, 12:58 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 34,098
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 use the R1C1 all the time. The R1C1 usefulness is pretty varied. First thing, its just a catesian coordinate system - so it can be pretty easy to explain to someone versed in math. Another is that formulas are not dependent on where they are located or what they are refering to. Third is that it makes programming easier. Plus, functions like *lookup and offset are so much easier to think about in the R1C1 world than the A1 world.

Lets say you wanted to do the Fibonnacci series in Excel. In A1 you put 1, and in B1 you put a 1. C1 would be A1 + B1, D1 would be B1 + C1, E1 would be C1 + D1 etc.

In R1C1 notation, R1C1 would have 1, R1C2 would have 1. In all the other cells, the formula would be RC[-2] + RC[-1]. If this formula is wrong in one of the cells, you can quickly scroll through the cells with the formula and look for a flicker where the formula changed and changed back.
Reply With Quote
  #10  
Old 04-11-2007, 01:02 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 34,098
Default

Quote:
Originally Posted by Phil View Post
I've also heard that if you convert a whole column to R1C1 display and press Ctrl-' to make Excel display formulas, then everything in that column should be identical and you can easily notice inconsistencies. For example, if reading down Column C, it reads

=A1+B1+D1*F1
=A2+B2+D2*F2
=A3+B3+D3*F3
=A4+B4+D3*F4
then you might not notice that cell # 4 is inconsistent

But in R1C1 format they will all read
=R[-]1[0L[1[-1][1]l]1[-1]0]C]!1[+1[[-1]-1

and then it's easy to find the one that's different.
The formula would not read that way.

It would read
=RC[-2]+RC[-1]+RC[1]*RC[3]

or it would read

=RC1 + RC2 + RC4*RC6.
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 11:25 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.15414 seconds with 10 queries