When we’re talking to potential clients about advanced analytics we often ask them what tools they’re currently using. More often than not they say they’re using spreadsheets. Spreadsheets are one of the most widely used tools for statistical analysis and of course, most businesses couldn’t run without them.
However, when it comes to advanced analytics spreadsheets have some very significant limitations. Use them beyond their capabilities and the potential cost can be significant. As with anything, it’s important to use the right tool for the job. So, what are the things you need to consider when it comes to using spreadsheets for advanced analytics?
What’s the scale of the job?
Most spreadsheets have a limit on the number of records that they can handle, so if you’re analysing a very large dataset with many cases or many fields, a spreadsheet will often struggle or many not be able to handle your data at all, in which case a purpose-built advanced analytics tool is what you need.
How complicated is the job?
Spreadsheets are fine if all you need is a superficial review of your data. However, if you want to unearth valuable information hidden within your data that isn’t immediately obvious, or you want to search for hidden patterns or conduct some other kind of complex analysis then a spreadsheet won’t be able to do any of these things for you as that’s just not what spreadsheets are built for.
How important is accuracy?
Spreadsheets aren’t designed with large datasets or complex calculations in mind and can be error-prone. If accuracy is important, for example if you’re making business decisions based on the outcome of your calculations, then another more purpose-built tool would probably be a better choice.
What would be the consequence of error?
Spreadsheets are highly prone to error. This is because essentially what you’re doing when you build a spreadsheet is writing a computer programme. Computer programmes are subject to coding error – generally it takes a massive amount of testing and de-bugging to ensure that a programme is working correctly and producing the right numbers. The same is true of a complex spreadsheet.
However, whilst computer programmes are generally written by teams and then double and triple checked before being deployed, spreadsheets are usually the work of one person and are almost never checked or verified by anyone else. Studies suggest that up to 90% of spreadsheets have errors in them, errors which can be extremely costly if the spreadsheets are being used as the basis for management decisions. Famously errors in spreadsheets were responsible for costing the Fidelity Magellan Fund around £1.6 billion (in that case the simple omission of a minus sign) as well as for the British government’s botched efforts to assess bids for the West Coast Rail Line in 2012, which is estimated to have cost around £50 million ($76.6 million).
Dedicated and purpose-built analytics tools are much more robust in this regard. Tools like SPSS Statistics have sophisticated built-in data validation and error-checking mechanisms that are designed to catch the kind of anomalies that can easily be missed in spreadsheets.
Download our free white paper to find out more
Spreadsheets are widely used for statistical analysis; and while they are incredibly useful tools, they are useful only to a certain point. When used for a task they’re not designed to perform, or for a task at or beyond the limit of their capabilities, spreadsheets can be somewhat risky as I have discussed. To find out more download our free white paper, which presents some points you should consider if you use, or plan to use, a spreadsheet to perform statistical analysis. It also describes an alternative that in many cases will be more suitable.
