MrExcel.com - Your One Stop for Excel Tips & Solutions

=SUM(B1:B5) is Better Than =B1+B2+B3+B4+B5

July 27, 2022 - by

=SUM(B1:B5) is Better Than =B1+B2+B3+B4+B5

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.

A spreadsheet shown in a billboard ad for the Microsoft Surface in 2013. Someone is planning a trip to Hawaii, but the grand total formula at the bottom is off by $500.
Figure 585. 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.

The right way to build the total formula. =SUM(E1:E6).
Figure 586. Right way.
A total formula is shown with =E1+E2+E3+E4+E5+E6. This is not the right way to sum these numbers.
Figure 587. 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:

Had the total formula used =SUM(E1:E6) and then someone inserts a new row 3, the formula automatically expands.
Figure 588. Right answer
When someone inserts a row and types $500 in the new E3, the formula does not pick up this cell: =E1+E2+E4+E5+E6+E7.
Figure 589. 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 Figure 586 after inserting a new row below row 7. Notice that the formula automatically changed to include =SUM(E1:E7)

Even if the new item was typed in row 7 just below the range in =SUM(E1:E6), the formula would expand to include the new value in E7.
Figure 590. Excel’s Intellisense corrected this potential error.

This article is an excerpt from Power Excel With MrExcel

Title photo by Antoine Dautry on Unsplash