Did I know this? The Excel Status Bar Statistics pick up the number format of the active cell in the grid. This is a useful feature, and not a bug. But it could freak someone out when you get different answers depending on if you select from bottom to top or top to bottom.
Also in this episode: the four zones of Custom Number Formats.
And, the winner(s) of a free Excel PDF.
Also in this episode: the four zones of Custom Number Formats.
And, the winner(s) of a free Excel PDF.
Transcript of the video:
Those stats down in the Excel status bar.
They use the number format of the active cell.
Did I know this? Did I know that the status bar stats, so right down here, you see the sum, max, min, numerical count, count, and average will follow whatever the active cell is.
So here the active cell is general, we get 6890.51.
But here the active cell is formatted with currency and a thousand separator, and we get $6,890.51.
Notice I don't have enough there to see if I'm getting a comma or a currency on the count, so let's put in a sequence function of 1234, select that whole range.
Format it as general, I get 1234 and 1234.
Format it as currency, I still get 1234 and 1234.
Okay, so let's change the rule.
Here's the rule.
"The statistics displayed in the status bar at the bottom of Excel use the number format of the active cell in the selection for average, min, max, sum, but not count or count numbs".
All right, there we go so, now we know.
A couple days ago, a few people that I knew kind of freaked out.
There was a forum posting somewhere and they said, "Hey, the Excel status bar is broken.
It is showing the wrong number".
Folks at Microsoft jumped right on that.
They said, "No, that's not what's happening.
Someone changed the format of the first number".
So I put this challenge out there and I said, "Check this out, this is kind of crazy.
This sums to 80, we know that, and if I come here, and select from the bottom I get a sum of 80 down here, but if I select from the top, I'm getting negative 80".
And I said, "Look, the first person who can tell me how I did this down in the YouTube comments gets a free Excel PDF".
Now for me, I'm an easy grader, right?
Solver Max, right away, five minutes later said, "It's the number format that hides the sign, and the status bar takes the number format from the first sale you select".
So for me, that's a winner.
However, then a few people started really getting specific.
And so if we want to do the hard grading, who gets it exactly correct, then it is, I don't know how to pronounce that, Cherian Iype.
All right, so hey, both of you send me an email to Mr.
Excel.
I'll send you out to the catalog, choose which Excel PDF you want.
So let's just talk about what we're going to call devious number formatting.
When we go into the number format and choose custom, there are four zones that we can type here, and the zones are separated by semicolons.
If all four zones are there, it's positive first, then negative, then zero, then what to do if it's text.
So here's a nice number format.
The positive numbers don't have a plus sign.
The negative numbers do have a negative sign.
The zero has a zero, and text is just displayed as text.
So we have 100, negative 100, zero, and hello.
It displays exactly as you would expect, but some people want a plus sign for the positive numbers.
All right, so that's easy enough.
We edit the positive zone to say plus zero.
All right, and we get to put a plus in there.
Some people want to do fancy things like have you owe a hundred bucks, you have a credit balance, no balance.
So by putting in that zone the stuff in text, we can replace that number with the word credit balance.
So the devious format is this one, where we're intentionally using the custom number format to show the positive numbers with a negative sign, the negative numbers with a negative sign, zeroes as zero, and text as text, right?
And see, that causes that a hundred, there's a hundred in there, but it's displaying as -100.
So how did the challenge work?
I came here, I actually typed negative 10, that forces that 80 to be correct.
So 10 times nine is 90, minus 10 gets 80.
But then I went in, control one, chose custom, and typed this, "Minus zero for positive, minus zero for negative, zero for zero, and @," click okay, and that very easily sets up the best way for you to win a free beer at the bar where all of your Excel friends hang out.
By the way, thanks to my friend Brad who pointed out the best name for that bar where all Excel people to hang out would be called The Formula Bar.
All right, so that's exactly how it happened.
Now, a few people said, "Hey, this is a bug, right?" I don't think that's a bug.
If it didn't work this way, it would drive us all crazy.
Here's a bunch of numbers in scientific notation.
If I would start here in a general cell, then I get just a boatload of numbers.
But if I start in the cell that's formatted a scientific notation, I get scientific notation.
Here's another one.
Here's all of our sales reps and when they met their quota for the month.
If I select all of these dates, I could very easily come here and see that the earliest quota was on February 4th.
The latest was on February 25th, and the average was on February 13th.
Again, if I selected from the general cell, then it is chaos.
I have serial numbers there instead of formatted dates.
Now it does create some potential for really bad things.
Here's four 410,407 from these numbers.
If I select these numbers, 410,407, but if I would select the whole column and not realize that data is stored as a serial number, then I get 455,395.
All right, yeah, so you just got to be careful.
Make sure to choose carefully what you want to have in the status bar.
And if you have some cells that are formatted and some cells that are unformatted, choose the formatted cell first.
And when I originally had this, the original name of this file in that first YouTube short was Puzzle For Bob Umlas, I always send these to Bob Umlas to see if he knows, because he's the Excel trickster.
He said, "Good one".
But then he gave me this awesome little trick here, he said, "Just use the custom number format of 33," right?
So that's a 10 up in the top cell.
I select all of those cells.
And down here the sum is 33, the max is 33, the min is 33, and the average is 33.
Count and numerical count, of course are correct.
There are 10 numbers there.
There's a lot of potential here for a great April Fools day trick, which is just coming up at the end of the month.
So passing this along, actual information here that maybe I knew, but maybe I didn't know that the status bar uses the active cell number format to display at least four of those six statistics.
Well, hey, I want to thank you for stopping by.
Thanks to everyone who commented on YouTube with their ideas about how to solve it.
We'll see you next time for another netcast from MrExcel.
If you liked this video, please visit tl.page/MrExcel.
Scroll down and you'll find my group lessons and plenty more videos.
Thanks for watching.
They use the number format of the active cell.
Did I know this? Did I know that the status bar stats, so right down here, you see the sum, max, min, numerical count, count, and average will follow whatever the active cell is.
So here the active cell is general, we get 6890.51.
But here the active cell is formatted with currency and a thousand separator, and we get $6,890.51.
Notice I don't have enough there to see if I'm getting a comma or a currency on the count, so let's put in a sequence function of 1234, select that whole range.
Format it as general, I get 1234 and 1234.
Format it as currency, I still get 1234 and 1234.
Okay, so let's change the rule.
Here's the rule.
"The statistics displayed in the status bar at the bottom of Excel use the number format of the active cell in the selection for average, min, max, sum, but not count or count numbs".
All right, there we go so, now we know.
A couple days ago, a few people that I knew kind of freaked out.
There was a forum posting somewhere and they said, "Hey, the Excel status bar is broken.
It is showing the wrong number".
Folks at Microsoft jumped right on that.
They said, "No, that's not what's happening.
Someone changed the format of the first number".
So I put this challenge out there and I said, "Check this out, this is kind of crazy.
This sums to 80, we know that, and if I come here, and select from the bottom I get a sum of 80 down here, but if I select from the top, I'm getting negative 80".
And I said, "Look, the first person who can tell me how I did this down in the YouTube comments gets a free Excel PDF".
Now for me, I'm an easy grader, right?
Solver Max, right away, five minutes later said, "It's the number format that hides the sign, and the status bar takes the number format from the first sale you select".
So for me, that's a winner.
However, then a few people started really getting specific.
And so if we want to do the hard grading, who gets it exactly correct, then it is, I don't know how to pronounce that, Cherian Iype.
All right, so hey, both of you send me an email to Mr.
Excel.
I'll send you out to the catalog, choose which Excel PDF you want.
So let's just talk about what we're going to call devious number formatting.
When we go into the number format and choose custom, there are four zones that we can type here, and the zones are separated by semicolons.
If all four zones are there, it's positive first, then negative, then zero, then what to do if it's text.
So here's a nice number format.
The positive numbers don't have a plus sign.
The negative numbers do have a negative sign.
The zero has a zero, and text is just displayed as text.
So we have 100, negative 100, zero, and hello.
It displays exactly as you would expect, but some people want a plus sign for the positive numbers.
All right, so that's easy enough.
We edit the positive zone to say plus zero.
All right, and we get to put a plus in there.
Some people want to do fancy things like have you owe a hundred bucks, you have a credit balance, no balance.
So by putting in that zone the stuff in text, we can replace that number with the word credit balance.
So the devious format is this one, where we're intentionally using the custom number format to show the positive numbers with a negative sign, the negative numbers with a negative sign, zeroes as zero, and text as text, right?
And see, that causes that a hundred, there's a hundred in there, but it's displaying as -100.
So how did the challenge work?
I came here, I actually typed negative 10, that forces that 80 to be correct.
So 10 times nine is 90, minus 10 gets 80.
But then I went in, control one, chose custom, and typed this, "Minus zero for positive, minus zero for negative, zero for zero, and @," click okay, and that very easily sets up the best way for you to win a free beer at the bar where all of your Excel friends hang out.
By the way, thanks to my friend Brad who pointed out the best name for that bar where all Excel people to hang out would be called The Formula Bar.
All right, so that's exactly how it happened.
Now, a few people said, "Hey, this is a bug, right?" I don't think that's a bug.
If it didn't work this way, it would drive us all crazy.
Here's a bunch of numbers in scientific notation.
If I would start here in a general cell, then I get just a boatload of numbers.
But if I start in the cell that's formatted a scientific notation, I get scientific notation.
Here's another one.
Here's all of our sales reps and when they met their quota for the month.
If I select all of these dates, I could very easily come here and see that the earliest quota was on February 4th.
The latest was on February 25th, and the average was on February 13th.
Again, if I selected from the general cell, then it is chaos.
I have serial numbers there instead of formatted dates.
Now it does create some potential for really bad things.
Here's four 410,407 from these numbers.
If I select these numbers, 410,407, but if I would select the whole column and not realize that data is stored as a serial number, then I get 455,395.
All right, yeah, so you just got to be careful.
Make sure to choose carefully what you want to have in the status bar.
And if you have some cells that are formatted and some cells that are unformatted, choose the formatted cell first.
And when I originally had this, the original name of this file in that first YouTube short was Puzzle For Bob Umlas, I always send these to Bob Umlas to see if he knows, because he's the Excel trickster.
He said, "Good one".
But then he gave me this awesome little trick here, he said, "Just use the custom number format of 33," right?
So that's a 10 up in the top cell.
I select all of those cells.
And down here the sum is 33, the max is 33, the min is 33, and the average is 33.
Count and numerical count, of course are correct.
There are 10 numbers there.
There's a lot of potential here for a great April Fools day trick, which is just coming up at the end of the month.
So passing this along, actual information here that maybe I knew, but maybe I didn't know that the status bar uses the active cell number format to display at least four of those six statistics.
Well, hey, I want to thank you for stopping by.
Thanks to everyone who commented on YouTube with their ideas about how to solve it.
We'll see you next time for another netcast from MrExcel.
If you liked this video, please visit tl.page/MrExcel.
Scroll down and you'll find my group lessons and plenty more videos.
Thanks for watching.
