Good news: The new Surface ads feature Excel. Bad news: there is a spreadsheet error in the ad. Read the original blog post from David Yanofsky here: Microsoft botched the Excel spreadsheet in its Surface tablet ad
This episode of the podcast looks at how the spreadsheet could be totaling incorrectly and teaches you why you should not use =B1+B2+B3+B4+B5. Also - how did they get the gaps in the pie chart?
This episode of the podcast looks at how the spreadsheet could be totaling incorrectly and teaches you why you should not use =B1+B2+B3+B4+B5. Also - how did they get the gaps in the pie chart?
Transcript of the video:
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.
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.
