Ask around on the street, and you will find that most people would say that they are “bad at math.” While we could spend an entire post talking about why that’s a bad thing and the ways to fix it, we should focus on the fact that these people who are self-identified “bad math users” probably use complex computational software on a daily basis: Microsoft Excel.
Many spreadsheets that are in use today are subject to bad calculations, bad results and bad data. Yet they continue to be used as major decision-making tools. Importing that data into a business intelligence platform won’t suddenly make it clean and accurate.
Forbes contributor Tim Worstall said “Microsoft Excel Might Be The Most Dangerous Software on the Planet” in light of how complex it has become and that it’s handled in such a slapdash manner. Close to 90 percent of spreadsheets are found to have errors in them.
“Every study that has attempted to measure errors, without exception, has found them at rates that would be unacceptable in any organization,” said Richard Panko of the University of Hawai’i.
And Rebecca Burn-Callender of The Telegraph quoted F1F9 statistics to report that “almost one in five large businesses have suffered financial losses as a result of errors in spreadsheets,” warning that businesses could lose billions of dollars because of bad spreadsheets.
UPDATE: Accountants also spread the word that spreadsheets can be disastrous.
AccountancyAgeInsight.com revealed that 73% of CFOs worry about their reliance on spreadsheets as detailed in a briefing on “Over-Reliance on Spreadsheets Spells Trouble for Firms Battling Solvency II.” And 80% of them have reported a fault with their planning processes when using spreadsheets over the past year.
This follows another briefing in February that found “17% of Large Businesses Blame Bad Spreadsheets for Financial Loss.”
If you are worried that your company or team may be subject to these purveyors of bad math, look for these causes of spreadsheet errors.
Garbage In, Garbage Out
The first day of Computer Science 101 (no, that class probably doesn’t exist, but let’s use it as an illustrative example), budding computer programmers are taught the principal of Garbage In, Garbage Out (G.I.G.O. for short). What it means is that your computer programs are only as good as the data that is input into them. If you input bad data, you get bad data.
Long complicated spreadsheets often have bad data littered all through them, data that is hastily input, never double-checked but still used to make critical calculations. As the saying goes, all that these spreadsheets will produce is something fit for the circular file.
Bad Formula Copies
Isn’t it great that when you copy a formula from one column (or row) to the other, it changes the cell references to reflect your new column (or row)? Until, that is, you need those references to remain the same.
Cutting and pasting in spreadsheets is very tricky. If you aren’t careful, you may find your data references changing, which means that your calculations are incorrect. Yet this happens quite often. Many mistakes in spreadsheets could be avoided by carefully checking cell references to make sure that they contain the values you expect them to contain.
Please Excuse My Dear Aunt Sally
No, Aunt Sally hasn’t given me a bad spreadsheet; that is the common mnemonic used to remember the order of operations. Another thing we learn in CompSci 101 is that sometimes software doesn’t play nice with that rule. Often you must explicitly order your operations by properly using parentheses (the “please” part always holds true); otherwise, your calculations are incorrect. Spreadsheets are no exception. These errors can be very difficult to find in a hastily created spreadsheet with a very long formula.
Misunderstanding Built-in Functions
It’s great that Excel provides us with a wide array of built-in functions, but unless you understand the subtle difference between the AVERAGE and AVERAGE A functions, you may actually be using the wrong built-in for your applications. This is another difficult error to detect, and sometimes it takes an excel expert to spot the error.
Business Intelligence Software Can Help
When users export data to perform analysis, there is a high risk of errors. It’s also a poor customer experience – increasing the likelihood they will look at other solutions. Stop requiring that your application’s end users export data into Excel for ad hoc analysis by giving them self-service BI seamlessly integrated into your application.