Problem: You have an intern working in the Accounting department who likes to add up short columns of numbers using =B1+B2+B3+B4+B5. You try to convince him that the correct formula is =SUM(B1:B5). He looks at you like you are insane, pointing out that his formula returns the exact same answer.
Strategy: Explain the story of the ad agency for Microsoft who plastered millions of dollars of billboards across America with ads for the new Surface tablet. The person who created the spreadsheet in the ad used a formula of =B1+B2+B3+B4+B5+B6 instead of =SUM(B1:B6) and ended up with a calculation error in all of the ads.
- The real total is $9500, not $9000.
Here is what likely happened.
When the ad was created, they forgot to put the car in the ad. The total was $9000. The figure on the left shows the right way to do the formula and the figure on the right shows the wrong way to do the formula.
-
Right way.
- Wrong way.
I am not sure why someone at the ad agency decided a car had to be included in the ad. But for whatever reason, someone went back after the spreadsheet was created and inserted row 3 with a $500 car rental.
Here are the results after adding row 3:
- Right answer
- The answer in the Surface ad.
The right formula"¦ the =SUM(E1:E7) automatically expanded to include the new row. The wrong formula, the =E1+E2+E3+E4+E5+E6 is now =E1+E2+E4+E5+E6+E7 and you have the wrong total.
Plus... there is no nagging green triangle warning you that the formula omits adjacent cells! This isn't the type of formula that error checking would handle.
This was not a real spreadsheet. This wasn't even a real couple planning a trip to Hawaii. It was just a silly ad showing that the Surface can run two apps side by side when the iPad would not do that. But, because they let someone in the marketing department build the spreadsheet, they ended up with a larger-than-life spreadsheet error plastered on billboards across the country.
It is interesting to note that even if the car would have been added to a new row outside of the =SUM(E1:E6) range, the formula in the total row would correctly rewrite itself. Here is Fig 601 after inserting a new row below row 7. Notice that the formula automatically changed to include =SUM(E1:E7)
- Excel's Intellisense corrected this potential error.