How to visually present statistical values (+/-) in a cell
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey welcome back, it's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, here live with Mike Girvin from ExcelIsFun, we're here at Highline Community College!
I'm out here for the week at the MVP summit, someone sent this question in via YouTube, and wow, they were adamant that they had to have data typed in like this, +/- and a number, and they wanted the amount +/-.
Can you believe it?
Oh my gosh, I said “Mike, you've got to be kidding me!” Alright, so my first impression is =LEFT, I said “Mike, is it always going to be centimeters?” He said “Yeah, alright.” Well, that makes it a little bit easier.
So we need the LEFT of this, but it might be 1, it might be 10, it might be a 100, so I never know how much I want, to get so I need the length of that -3.
Let's just see how that works, I'm hoping to get a 1 there, alright, that's sweet!
OK!
Then +, alright, that top one, I'm looking at that, I need the MID of that number, ,4, oh geez, I have to do some counting, length of that whole big thing, let's see, -4-7, that is cool!
Now that that's working, I'm going to use the great Ctrl+’ trick, which will take that exact formula and copy it down.
And then all I have to do is change the plus to a minus, that Ctrl+’ trick is pretty cool, because it brings the format down without changing the ranges.
Alright, I have that formula, it's working, all I need to do is this, there we go.
What an evil problem, but that's my solution!
Mike, what have you got?
Mike: Whoa, Ctrl+’, that is pretty slick.
I always got to scoop it out, copy and paste it up, I have to remember that one!
Well, what if you don't have to type this in, but we could present it visually with custom number format?
How about .05 here, and 1!
And I'm going to come here and Ctrl+1, and we want some text, we want the text to appear there, but have the, really, just a number in the cell, so we can do a calculation with that number.
So I'm going to go down to Number tab, Custom, and click under Type, and now “+/- “, and it looks like we want two decimals showing, so I'm going to type a 0.00, ” cm”.
You see the sample emerge up here looking alright.
Oh look at that, now looks like it's there, but sure enough it's not, and if you hit F2, boom!
And you could change that number to whatever you want, and then the formulas down here will update.
This one, Ctrl+1, Custom, how about 0.00 “ cm”.
Alright, so now it looks like that has text in the cells, but when we do our formula, equals this, F4, plus this, F4 and then I'll copy it down.
Not as slick as Ctrl+’, and I'll change that.
Alright, so there you have it, uhhh… Give it to MrExcel!
Bill: Alright, yeah, that's uh, that's pretty sweet!
So what if this was like .1 though?
So now you're going to have an extra zero there all the time, so I'm going to go to Ctrl+1.
What if we change that, instead of 0.00, what if we do 0.0#, that pound sign says “Hey, you put a digit there if you need it, but if you don't, then it doesn't show up.” And so now, if we have .05, and if we have .2, the extra zero doesn't show up there, and we don't really care, because we don't have to use the LEFT or the RIGHT!
What a horrible, horrible problem, you know, this is someone's managers make them do this.
You know, right, it's always the crazy manager to say “Oh yeah, it has to look like this!” What an evil problem!
Well, there you go, hey, it's another Dueling Excel podcast from MrExcel and ExcelIsFun, see you next time!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey welcome back, it's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, here live with Mike Girvin from ExcelIsFun, we're here at Highline Community College!
I'm out here for the week at the MVP summit, someone sent this question in via YouTube, and wow, they were adamant that they had to have data typed in like this, +/- and a number, and they wanted the amount +/-.
Can you believe it?
Oh my gosh, I said “Mike, you've got to be kidding me!” Alright, so my first impression is =LEFT, I said “Mike, is it always going to be centimeters?” He said “Yeah, alright.” Well, that makes it a little bit easier.
So we need the LEFT of this, but it might be 1, it might be 10, it might be a 100, so I never know how much I want, to get so I need the length of that -3.
Let's just see how that works, I'm hoping to get a 1 there, alright, that's sweet!
OK!
Then +, alright, that top one, I'm looking at that, I need the MID of that number, ,4, oh geez, I have to do some counting, length of that whole big thing, let's see, -4-7, that is cool!
Now that that's working, I'm going to use the great Ctrl+’ trick, which will take that exact formula and copy it down.
And then all I have to do is change the plus to a minus, that Ctrl+’ trick is pretty cool, because it brings the format down without changing the ranges.
Alright, I have that formula, it's working, all I need to do is this, there we go.
What an evil problem, but that's my solution!
Mike, what have you got?
Mike: Whoa, Ctrl+’, that is pretty slick.
I always got to scoop it out, copy and paste it up, I have to remember that one!
Well, what if you don't have to type this in, but we could present it visually with custom number format?
How about .05 here, and 1!
And I'm going to come here and Ctrl+1, and we want some text, we want the text to appear there, but have the, really, just a number in the cell, so we can do a calculation with that number.
So I'm going to go down to Number tab, Custom, and click under Type, and now “+/- “, and it looks like we want two decimals showing, so I'm going to type a 0.00, ” cm”.
You see the sample emerge up here looking alright.
Oh look at that, now looks like it's there, but sure enough it's not, and if you hit F2, boom!
And you could change that number to whatever you want, and then the formulas down here will update.
This one, Ctrl+1, Custom, how about 0.00 “ cm”.
Alright, so now it looks like that has text in the cells, but when we do our formula, equals this, F4, plus this, F4 and then I'll copy it down.
Not as slick as Ctrl+’, and I'll change that.
Alright, so there you have it, uhhh… Give it to MrExcel!
Bill: Alright, yeah, that's uh, that's pretty sweet!
So what if this was like .1 though?
So now you're going to have an extra zero there all the time, so I'm going to go to Ctrl+1.
What if we change that, instead of 0.00, what if we do 0.0#, that pound sign says “Hey, you put a digit there if you need it, but if you don't, then it doesn't show up.” And so now, if we have .05, and if we have .2, the extra zero doesn't show up there, and we don't really care, because we don't have to use the LEFT or the RIGHT!
What a horrible, horrible problem, you know, this is someone's managers make them do this.
You know, right, it's always the crazy manager to say “Oh yeah, it has to look like this!” What an evil problem!
Well, there you go, hey, it's another Dueling Excel podcast from MrExcel and ExcelIsFun, see you next time!
