

FlashChat  Actuarial Discussion  Preliminary Exams  CAS/SOA Exams  Cyberchat  Around the World  Suggestions 
Browse Open Actuarial Jobs 

Thread Tools  Search this Thread  Display Modes 

#1




"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 
#2




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? 
#3




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?

#4




Quote:
=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. 
#5




Quote:
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). 
#6




Whoops, I did say ' and apparently it should be `. You can tell I never use this.

#7




Quote:
It would read =RC[2]+RC[1]+RC[1]*RC[3] or it would read =RC1 + RC2 + RC4*RC6. 
#8




How do you know your actuarial system is right?
Please allow me to rephrase the question.
If your actuarial system is developed by the IT department or purchased from a third party vendor, how do you QA the system? You should do exactly the same thing when you review your own spreadsheet. However, the challenge is that most spreadsheet is developed, managed, operated and tested by the same person. There is no segregation of duties! I can't recall how many times I saw a spreadsheet was still modified the night before the results was submitted to CFO in the next morning. There is no regression test, no walk through, no control. They probably did not recall what they had changed. Good Luck! I don't know about other people but it is always easier for me to review other people's work than my own work. Excel application is just like all other systems. If other systems require software development life cycle (SDLC) to manage, why not Excel? Most Excel spreadsheet can be very dynamic and unstructured, i.e. very difficult to review. I had worked with program that a single function had thousand lines of code. My record is 10,000+ lines in a single function. I can write a book about anti design pattern from this function. We need to follow industrial design guideline / best practices to make sure our applications are robust. This is nothing new, call your IT department and they may be to share with you their SDLC (both infrastructure and methodology) and design guideline. However, the biggest challenge is why we develop Excel applications. We use Excel instead of engaging IT because we want to deliver it ASAP and we are willing to cut corner! An Excel application took more than a year to develop, it has over a million lines of VBA code and hundred of million of Excel formula. The review was done in 2 weeks. Super fast! The review must be done in 2 weeks so that it could be released to meet the deadline!
__________________
AC. 
#9




Quote:
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. 
#10




When I copy and multipaste a formula, I expect it to paste the same relative formula all the way down. I don't see how it could get pasted incorrectly. Of course, I'm usually perfect, in every way.
One formula that does deserve some scrutiny is "OFFSET". Even when hitting F2, this formula doesn't show (in different colors) which cells are actually being computed. Checking the formula's result with the specific range that is intended to be calculated (be it averaged or summarized) is paramount.
__________________
"Facebook is a toilet."  LWTwJO "45 es un titere"  Seal of The President of The United States of America protest art 
Tags 
machine learning, predictive analytics, speadsheet error, spreadsheets 
Thread Tools  Search this Thread 
Display Modes  

