First a disclaimer, I am not a fan of spreadsheets. There I’ve said it and I know it’s sacrilege to some.

Don’t get me wrong I use them as much as the next person but I’m always very careful in creating a spreadsheet based solution. As a veteran IT professional I’ve seen too many spreadsheets that should come with a warning slapped on them.

In the best cases they are often over complicated in construction and built without a thought as to what they may need to become in the future. However, in the worst cases they just plain don’t add up. Incorrect references and formula and the most eye straining designs you’ll ever have the misfortune to witness coupled with an obvious misunderstanding of the concept of a spreadsheet all mean too many spreadsheets are just not fit for purpose.

Be it a lack of training or a laissez faire attitude on the part of those who should know better in an organization in my experience it is a pervasive problem in organizations today.
Anyway, climbing down from my soapbox …

History

To add to this dismal situation Excel has a long history of calculation malfunctions!

Just to clarify I don’t mean when a formula is incorrectly formulated by a user. These calculation malfunctions manifest themselves in various situations even when the details entered are completely correct.

What’s Wrong with Excel Now?

Well, the latest issue manifests itself when a collection of positive numbers should add up to a given negative number.

That is easier to show rather than describe so per the following screenshot of Excel, in each of the 3 examples the sum of positive values should add up to the negative value (on Row 1) therefore the result of the SUM in row 10 should be 0 – which as you can see it is not.

excel_calculations.jpg

For example in column B:

100.03 + 0.04 = 100.07

-100.07 + 100.07 should = 0

But according to Excel it is 7.95891E-15 . If you change the format of the cell to Number and increase the decimal places (see row 12) you can see the REAL values.

So as demonstrated Excel, prior to any forthcoming patches, has a problem with addition and/or subtraction especially evident when calculations involve a mix of negative and positive numbers as the result approaches zero.

Does it matter?

OK so does this really matter? My answer is a resounding “Yes” and in more circumstances than you may imagine.

1. Data Comparison Operations

Any time two numbers are compared you need to be aware that the number displayed on the screen isn’t the complete number Excel uses to make any required comparisons and that complete number might adversely affect the result of the comparison being made.

The most obvious comparison function is the IF statement. For example, an IF function that uses 0 in the first argument, as in =IF(B12=0, “True”,”False”)

However, comparisons in Excel are by no means limited to the IF statement alone. Indeed all the following functions use comparisons:

  • IF
  • LOOKUP
  • HLOOKUP
  • VLOOKUP
  • SUMIF
  • SUMIFS
  • COUNTIF
  • COUNTIFS
  • AND
  • OR
  • NOT

2. Scaling Errors with Large Multipliers

Another situation where the miscalculation can manifest problems is where extremely large numbers are used. This is because while the errors are many decimal places to the right of a number, multiplications using large numbers will serve to magnify the error.

Why does it happen?

These errors are a result of a floating-point rounding error and if that hasn’t already reduced you to a catatonic state you can read more about the details in Microsoft’s Knowledge Base article 214118.

Could it be avoided?

In a word – Yes and it should be. Programmers often have to write code to take into account failings elsewhere.

A personal allegory will serve as a demonstration.

Many years ago when Excel 2000 was state of the art and Excel 97 was the version of choice for a client of mine I undertook a project to develop an Excel based solution. Part of the solution required a form to be displayed to the user from which they selected a date from an OLE data control.

This worked perfectly on my Excel 2000 system but when deployed at the client’s site the date was constantly 1 day out. The user would select a date using the control but the date displayed was incorrect. After much testing it became clear that Excel 2000 and Excel 97 did not handle dates in the same way when using VBA code to manipulate the data.

Obviously I had to create a solution to work around the VBA anomaly introduced with Excel 2000. I created a simple code block to check the version of Excel in use and adjust the date accordingly.

Wringing my hands and complaining about known anomalies wouldn’t help the client and in this regard Microsoft should take the same approach. While floating-point issues may be the root cause of the problem they can be handled in such a way that the end user is not adversely affected.

A fact that has not escaped other software providers.

Is it only Excel?

Excel is not alone in is it’s miscalculations. Apple’s Numbers calculates incorrectly in exactly the same way.

However, the good news is there are many programs that handle the issue elegantly and calculate correctly, including:

  • OpenOffice
  • NeoOffice
  • Google Spreadsheets
  • ZoHo Sheets

In the meantime … what can you do?

There are several techniques available that may eliminate the issue from your spreadsheet.

However, bear in mind that these are only a temporary fix which shouldn’t be needed and may not work in every situation and the burning question how do you know when you’ll need it before the final figures are in?

Sorting

Reordering the listing of values can on occasion solve the problem. Obviously, this just serves to emphasize how ridiculous the problem is! The order in which figures are added should have no impact on the result and there are occasions where reordering items is not possible. For example, a list of sales figures sorted by month can’t be reordered to accommodate Excel’s idiosyncrasies.

Using Precision as Displayed

Another potential solution is the Microsoft recommended use of the ‘Precision as displayed’ option.

‘Precision as displayed’ is an option in the Excel Options the function of which is to force Excel to calculate using only the level of precision displayed in the cell rather than the full data stored within the cell.

This is an approach which should work well with accountancy and financial data where precision to two decimal places is required.

In other situations ‘Precision as displayed’ can actually cause problems of it’s own and certainly the data will be less accurate than with the option disabled.

In Excel 2000/2003 the Precision as displayed option is in the Calculation section of the Excel Options.

excel_options2000.jpg

In Excel 2007 the Set precision as displayed option is in the Advanced section of the Excel Options.

excel_options2007.jpg

Using the ROUND Function

The Round() function rounds a value to a specified level of precision. The values contained within the cells are not altered but the value used for comparative purposes is determined by the level of precision specified by the arguments in the ROUND function, as follows:

=IF(ROUND(B12,2)=0, “True”,”False”)

The use of the Round function means that the value contained within cell B12 is rounded down to 2 decimal places prior to it being compared to, in this case, zero thus removing the potential impact of any floating point errors to the far right of the decimal point.

The Round function adjusts a value up or down depending the threshold. If the significant digit is 1,2,3 or 4 it rounds down whereas if the significant digit is 5,6,7,8 or 9 it rounds up.

Conclusion

So there it is – an insight into the latest in a long line of Excel issues to affect the unsuspecting user.

It becomes clear that it is folly to rely on Excel to correctly compute even the most basic calculation. Best advice is check everything … at least twice!