Learn Excel – Summing for Ad Agencies – Podcast 1823a

Learn Excel – Summing for Ad Agencies – Podcast 1823a


Learn Excel from MrExcel, Podcast Episode
1823A: Why You Should Never Use=B1+B2+B3+B4+B5. Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen. Today’s question’s sent in by Crispin. No. I’m kidding. Hey, this is great. I ran across a blog post by David Yanofsky
yesterday writing at qz.com. So, that’s from November 5th. I’ll put the link in the YouTube description
right down there but check this out. So, David works in New York and he was down
in the Spring Street subway station and saw the print ad, “This is not just a laptop,
this is not just a tablet, this is the new Surface,” right? Nice ad. That’s great. I guess it’s all over the subway and bus stops,
but David took a closer look. Now, this happens to me sometimes in my Power
Excel Seminars, especially if I’m in front of a room full of auditors. They’ll actually check the math in my spreadsheets,
and David looked at this 2500 + 4000 + 500 + 1500. That adds up to 9500 and the total in the
spreadsheet is 9000. Oh. This is so bad, right? Excel has a bad reputation for spreadsheet
errors and here now an Official Microsoft ad is showing an obvious spreadsheet error. How bad is that, but then I started to think,
“How could the ad agency have screwed this up? What steps could they have possibly gone through
that Excel wouldn’t get the total right?” So, here’s the original numbers. It’s off by 500 which either means they added
CAR — which seems unlikely because that is an obvious travel expense — or DIVE RENTALS
or they added in the last two in order to get more color in the pie. So, let’s assume that they did that. Even if they would have done AutoSum – alright,
so, use the AutoSum function — and even let’s say that they explicitly chose just those
five, Excel can deal with this. If we insert two rows, and, let’s see, it’s
SURF RENTALS here and OTHER EXCURSIONS here — I’m going back to Excel 2003 — provided
you have a series of three or more numbers, they’re going to change the formula. They’re going to rewrite the formula to handle
that. So, the formula there just changed to include
B6 and then I type in 300 here and the formula changes to include B7. So how could the ad agency screw this up? Alright. Let’s go back. I’m going to undo, undo, undo, undo, undo. One obvious theory is they don’t know how
to add it all and they just simply put 9000 in there. That would do it. The other thing that’s possible is they built
the formula like this –=B1+B2+B3+B4+B5 — alright, and you get the right answer. Sure. I remember, back in 1985, using Lotus 1-2-3,
I did the same thing and then someone came along — one of the graduate students in Notre
Dame — and said, “Hey, no, you have to use @SUM I’m like, “Well, no, this works,” and
they said, “No. Use @SUM – 02:57,” and the reason you
want to use=SUM in Excel instead of this is for this very problem. So, you come along and let’s even add it in
the middle, right? SURF RENTALS of 200. See? The total doesn’t change and then here we’ll
add at the end OTHER EXCURSIONS. TOTAL is not changing. So, either they just typed the number there
or they used this really bad method of creating the formula which, sure, gave you the right
answer from the beginning but then doesn’t work after that. You know, then the other thing that got me, I started looking at this chart. Was this chart created in Excel 2013? So, I’m going to create a chart. If they would have used Recommended Charts,
it would have suggested the clustered bar chart, not the pie chart, but, ok, let’s say
that they chose the pie chart, and then triple click the title “HAWAII BUDGET,” made
the chart a little bit less wide. Alright, so, we’re starting to get there. We have the legend that matches. HAWAII BUDGET, we’ll probably need to increase
the size there a little bit. Alright. That’s starting to look good but this chart
still doesn’t look like that chart. First off, the colors for the last three slices
are different. So, I said, “Hey, that’s cool. Whoever did this might have known about the
chart styles and the new color swatches available out here,” but none of these colors match
the ones in the ad. Okay. Well, maybe they’ve been around Excel since
Excel 2007 and they know the colors palette back here on the PAGE LAYOUT. There’s forty different colors and I ran through
all forty of those and none of those match. MEDIAN gets sort of close but still they had
to change the one or two of those in Photoshop. Okay but then, even then, what’s up with these
big white gaps between the columns? Is that an exploded pie? It’s not even in the chart anymore. If you would go to CHART DESIGN, CHANGE CHART
TYPE, they don’t offer exploded pie. Yes, of course we can still get there using
CONTROL1, or select the series, CONTROL1, and then pie explosion, but I tried various
pie explosions and none of them really match what is shown in the ad. They kind of get too gap-y and move out from
the center, so it’s not a pie explosion. So, what did they do, how did that ad come
about with those big gaps, and then I realized what they must have done is selected the series,
come back here to SHAPE OUTLINE, chosen white as the shape outline, and then, still beyond
that, they would have to go in and change the WEIGHT. Yeah. Let’s try 3pt. That starts to get really close. Alright. That’s an impressive set of steps to click
here, then back on FORMAT, SHAPE OUTLINE, change to white, and then WEIGHT, and find
the right weight to get those gaps there in the chart. It’s really kind of amazing that they knew
that but not how to total. Alright, hey, again, go out to qz.com, check
out this article by David, follow him on Twitter, visualization guy, lots of great things. Hat tip to David for noticing this in the
subway and actually taking up the time to notice that the numbers don’t foot. What an embarrassing thing, you know. We finally have Excel in an ad and Microsoft
shoots itself in the foot with a spreadsheet error plastered all over the New York City
subway. Well, hey, I want to thank you for stopping
by. We’ll see you next time for another netcast
from MrExcel.

6 thoughts on “Learn Excel – Summing for Ad Agencies – Podcast 1823a

  1. I thought this was hilarious when I saw it in your Daily Excel. Gave me a smile to see it on youtube! Great example. I guessed they added in "Car" after the fact as well…

  2. Yes I agree. Once you started showing how the colors were off I realized the whole thing was "photoshopped". Make me wonder if they ever use their own software. lol….

  3. I would say Microsoft as a company and the advertising agency too are responsible for the "go ahead" to a billboard and tube campaign containing a blatant mistake. I understand Bill's advice on trying to avoid =B1+B2+…B5 for the greater risks associated with it. But all this having been said, Excel itself as a piece of software comes unscathed of this blunder and I'm still waiting to see a real Excel error, a situation where the user is misled by the tool itself and not by the carelessness, bad use or lack of understanding of the modeler, whale or no whale. Great video!

Leave a Reply

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