Actuarial Outpost

Actuarial Outpost (http://www.actuarialoutpost.com/actuarial_discussion_forum/index.php)
-   Software & Technology (http://www.actuarialoutpost.com/actuarial_discussion_forum/forumdisplay.php?f=17)
-   -   "How do you know your spreadsheet is right?" (http://www.actuarialoutpost.com/actuarial_discussion_forum/showthread.php?t=106704)

campbell 04-10-2007 01:28 PM

"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

BassFreq 04-10-2007 02:47 PM

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.

no driver 04-10-2007 04:41 PM

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?

BassFreq 04-10-2007 04:55 PM

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?

tommie frazier 04-10-2007 05:10 PM

i see it as useful, sure. not what I learned on, so that would take getting used to.

Phil 04-10-2007 06:07 PM

Quote:

Originally Posted by no driver (Post 2041684)
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.

dumples 04-11-2007 11:26 AM

Quote:

Originally Posted by Phil (Post 2041939)
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).

Phil 04-11-2007 11:37 AM

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

whisper 04-11-2007 12:58 PM

Quote:

Originally Posted by no driver (Post 2041684)
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.

whisper 04-11-2007 01:02 PM

Quote:

Originally Posted by Phil (Post 2041939)
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.


All times are GMT -4. The time now is 05:25 PM.

Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Page generated in 0.13542 seconds with 9 queries