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

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?

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? 
i see it as useful, sure. not what I learned on, so that would take getting used to.

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. 
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). 
Whoops, I did say ' and apparently it should be `. You can tell I never use this.

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