herbaltea001-winter (4/22/2013)
Aside from the errors introduced by individual functions, most spreadsheets have chained functions, each contributing its own error. On a good day the errors might cancel but good days are hard to find.
The same is true of any overall system which gathers data, transforms it, and aggregates it 😀 IT folks are just less confident that everything they do is 100% correct and put steps in (hopefully) to try and find errors before they cut millions of pounds off their company's net worth
I've found two ways to cut some of the more common errors in excel:
1) a standard layout for spreadsheets (summary[1:n], raw data, code/definitions) where the raw data in particular is rigidly formatted (PK on LHS, no column or row gaps etc) which encourages people to think about the different layers of analysis as well as documentation
b) a macro to build dynamic named ranges so that raw data can be referenced more safely by pivot tables, vlookups and [sum]ifs. This cuts down on the reference errors like incorrect $ signs and ranges not fitting all the data, and also greatly increases the ease at which someone can look at a formula and understand it e.g.
sumifs(balance, inceptiondate, ">="&reportstartdate, inceptiondate, "<="&reportenddate)
and
sumifs(sillySheetName!$A$2:$A$156732, sillySheetName!$D$2:$D$156732,">="&AZ1, sillySheetName!$D$2:$D$156732, "<="&AZ1)