The Credit Card Numbers
The other day, I read an amusing* article about a chap who recorded all of his credit card details on an Excel spreadsheet
When he subsequently retrieved this information, the last digit of some numbers had somehow changed, which inevitably caused a good deal of chaos.
Although an Excel spreadsheet looks simple enough, it actually makes a number of assumptions in an attempt to reduce the user workload—but these assumptions can sometimes give rise to spurious results.
For example, Excel can handle many different types of data including text, numbers, currency, dates, etc.—but, unless you specify the type of data contained in a particular cell, it will make assumptions based on the actual data entered.
Take a typical credit card number such as: 4508 0001 2345 6789
In Excel 2010 (may not be true for all versions), this will be interpreted as a TEXT field because it contains spaces.
However, it the number is typed in, without spaces, as 4508000123456789, it is assumed to be a floating-point number—that being the Excel format for handling very large numbers.
Even if you define the cell to display as a normal number, there will most-likely be an error because the internal format is still floating-point for a number of this size.
A shorter number such as 45080001234 doesn't exhibit these behaviours.
Note that while floating-point numbers have a very high precision, they are always an ‘approximation’ and may not give the desired result in accounting-type applications.
The quick answer here is to recognise that this so-called credit card number is actually a credit card ‘reference’ and not intended for any sort of calculation—it is TEXT and should be specifically defined as such.
*I am easily amused
Rounding Issues
The following picture illustrates a common issue with Excel spreadsheets—the figures in the total column don't actually add-up to the overall total arrived at by 'summing' those figures.
In this second, apparently identical, spreadsheet, the overall total is correct.
By increasing the number of decimal places in two of the columns, it becomes clear that the VAT calculation sometimes generates results which include fractions of a penny and these are rounded when the figure is displayed to 2 decimal places.
However, this rounding of the displayed numbers does not necessarily change the underlying numbers used for calculation so adding-up the two lists (rounded and unrounded) will often produce a significantly different result.
For most commercial accounting purposes, working to the nearest penny is good enough and any greater accuracy in the underlying numbers is going to create these sort of rounding problems—though the same sort of issues could potentially arise with several decimal places displayed.
The answer to this issue is buried in the Excel 'Options' (Version 2010 is shown)—if you set the 'precision as displayed' option, the underlying numbers will be rounded identically to the displayed numbers and the problem is resolved.
Ironically, this generates a message stating that you will permanently lose accuracy though, to my mind, this is the most useful setting for simple accounting applications.
The bigger lesson to be learned here is that, when creating a new spreadsheet, you should always test to ensure that it is actually producing the expected results.
