David Yanofsky was riding the New York City subway when he noticed an ad for the new Surface tablet in the Spring Street station. The ad showed a budgeting spreadsheet used in planning a trip to Maui running in Excel 2013 on the Surface tablet. The budget has seven line items, including ones for air, hotel, car, surfing and diving. Unfortunately, the total at the bottom was wrong, as Yanofsky’s photo on Quartz.com shows.

How did it happen?

If you were creating an Excel worksheet and had to total five numbers, an Excel rookie might use a formula of =B1+B2+B3+B4+B5 as shown here:

Excel pros would instead use the AutoSum button and end up with =SUM(B1:B5).

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)

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

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.

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.*

Sagara Fernandosaid 11/08/13 06:32amMicrosoft 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.

Richard Balagursaid 11/08/13 10:28amEven 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.

GloriaBsaid 11/11/13 11:01amIt 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.

Patrick O'Beirnesaid 11/13/13 04:22amThis 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

Vinod Bhatiasaid 11/13/13 05:02am…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

Adrian Maconicksaid 11/13/13 09:32amThey would not be the first organisation to get this wrong. Putting in effective controls over spreadsheets is a key part of using them. http://finsburysolutions.com

Shefsaid 11/13/13 17:49pmSo 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.

QIMacrossaid 11/14/13 10:15amExcel 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