• Spreadsheets
  • CFO.com | US

Even Microsoft Needs Help Avoiding Spreadsheet Errors

The cells didn't add up in a Microsoft Excel ad posted in the New York subway system. Here's why.

Both return an answer of $9000, so the spreadsheet rookie might argue that the original answer is correct and just as good as using the AutoSum button.

Although both methods return the same answer, the AutoSum is the right way to do this. Excel has smarts built in that make sure the erroneous answer in the ad will not happen if you use AutoSum. When you insert new rows in the budget, even at the end of the budget, the AutoSum formula will automatically adjust. As soon as you type in a number in B6, the Total formula becomes =SUM(B1:B6)

ExcelError3

Fill in the number in B7 and the total adjusts again:

ExcelError4

These adjustments only happen if you use the SUM version of the formula. If you used the original formula and add new rows, even rows in the middle of the budget, the formula will never adjust, and you will end up with the wrong result shown in the Microsoft ad.

ExcelError5

Crazy things happen when marketing people try to use Excel. This is just the latest example, one in which Microsoft’s ad agency produced an interim result that “looked” OK, but could not adapt.

Bill Jelen is a contributing editor and the host of MrExcel.com. He points out that his book, “Don’t Fear the Spreadsheet,” warns against this exact type of formula.

8 thoughts on “Even Microsoft Needs Help Avoiding Spreadsheet Errors

  1. Microsoft Excel is dumb and it is not capable of think and make errors right by its own therefore, we must ensure our arguments and logic are perfectly all right based on these only excel gives its feedback. I have experienced some companies really go nuts with spreadsheets as their logic and functions are inaccurate and eventually giving a flawed answer. However, if you use it accurately then it may be the best tool available in the market by considering its functionality.

  2. Even better than summing rows 1 through 5 would be to leave a blank space at the top and bottom of the area to be totaled and include these within the =Sum formula. This allows for added rows, even adding one below the last function.

    A check sum of some sort is also helpful, whether its percentages which should total 100% or tabulating rows and columns and then checking that the sum of the rows equals the sum of the columns.

  3. It may well have been a deliberate mistake. The human mind is sensitive to things not quite right. After all, look at the free PR they’ve gotten because of the mistake.

  4. This is a funny story about marketing people, but it does help make a more serious point about checking your final output before it causes reputational damage. For some people, the damage can be real, whether in financial loss or even safety calculations in engineering. The European Spreadsheet Risk Interest Group (EuSpRIG) takes a professional interest in risks in end-user computing and has an annual conference to discuss ways to prevent & detect errors and improve productivity and efficiency in the use of spreadsheets. The next is in Delft in July 2014, see http://www.eusprig.org

  5. …or they were trying to be too clever and used =ROUNDDOWN(SUM(B1:B8),-3) to display the total down to the nearest thousand

    …or maybe the SUM formula is right, but they’ve used a custom format to round down (is that possible?)

    Do you think we deserve an explanation from Microsoft. Not sure this is the sort of free PR that they want for their flagship office tool

  6. So typical of non financial people to not 2x check their work. Real Accountants and Finance people are taught that immediately: ALWAYS 2x check your work. This is a easy mistake to make because the formula only adds the cells you tell it to and that doesn’t include a series of cells (=sumA3.A21) if that is the result that is desired. Who the hell cares about a graph if the answer is wrong.

  7. Excel is as smart as the user. Try Excel add-in QIMacros for Excel for a low cost and easy way to make the data relevant and informative

Discuss

Your email address will not be published. Required fields are marked *