Bill thinks he has a 'killer solution' for this Dueling Podcast - lets find out! Today, in Episode #1359, Mike 'ExcelIsFun' Girvin and Bill 'MrExcel' Jelen find solutions that will Round Dollars to (2) decimals, but Round Crude to (4) Decimals.
Transcript of the video:
Hey, welcome back. It's another dueling excel podcast. I'm Bill Jelen from MrExcel.
We're joined by Mike Gel Gervin from Excel Is Fun. We're here live at Highline Community college.
I told Mike he has to go first on this one because I think I think I have a killer solution But I don't want mike to know what it is. I can't wait. I've been around to get this done. All right Here's the setup someone just said I need to round But guess what every time there's a category dollar round to the 4 otherwise if it's a crude 2.
So I'm going to go ahead. I just use an IF but it'll be inside the round so = ROUND.
Where's the number it's right there comma and number of digits while we need a 4 or 2 so it's simply IF this = and I'm going to click on this and then double click it and hit F9 because I'm such a bad typer Anytime that's equal to that, then what do I want.
The value if true.
Well that's a logical test that comes out true or false.
You know I think dollars be 2 and crude should be 4. I think we have it backwards, okay, suit that way, so value of true It's going to be 2 , otherwise it's 4. Now this formula assumes that there are only dollars and crude in the column. All right a close parenthesis and all.
The IF is doing is delivering one of two numbers to that argument.
Double click and send it down. Now. I'm going to cheat. I'm going to do it another way also You know we could do the same round But what if we had a table over here.
And we had dollar and crude actually maybe you do it down here just for the video and we had our 2 and 4 This example We'll use VLOOKUP inside the round but this would accommodate if you had many different categories that require different rounding.
So I'll just do the round of this comma VLOOKUP and the lookup value it's going to be that category , the lookup the table array and F4 comma column that's the second column we need to return the item from 2 and then comma False so I'm going to put 0 for an exact match that's the vlookup inside of numbers VLOOKUP and send it down and this one's has the decimals, but there's two ways to do. All right. I can't wait Wait a sec. I don't like that one because it's showing way too many decimals, so you know, I'm not sure about the person who sent this question. I don't think they really need it rounded.
I think they just want to display it rounded. I think they just want to display it rounded. I'm going to come here I'm on it. This is new you can't do this before excel 2007. I'm going to go to Home Conditional Formatting New Rule Use a formula to decide which cells to format and the formula is going to be = so I'm sitting right now in A2 So I'm going to look one cell to the right to me =B2 ="DOLLAR" If that's true when I click the Format check this out.
They added a number of formatting.
So number formatting I'm going to show two decimals for that particular row.
Click OK and then I'm going to take this whole column and kick it up to 4 decimals number 2 3 4 four click ok and then what happens is if the cell to the right of me is a dollar the Conditional Formatting kicks in and only gives me two decimal places.
Otherwise the default is I get 4 and it will display correctly.
"Conditional Formatting" and ofcourse the trick there is we're using a formula that is pointing one cell to the right in order to pull that off. Well what you think Oh the Number tab when did that come in in Conditional Formatting?
I think in excel 2007, it wasn't there in 2003 I know that.
And everyone wanted it.
Oh yes, it's I used it once in a book. I don't remember which book for If we had numbers that were like less than a thousand between a thousand a million and greater than a million I would show either comma k or comma comma m depending on how big a number was it was just on the fly.
Amazing right that's what's so great about these tools. We just learn amazing things oh i never thought.
I didn't think of until Mike starts recorder. Oh wait you go first. I got one All right, well hey I want to thank everyone for stopping by.
Will see you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
We're joined by Mike Gel Gervin from Excel Is Fun. We're here live at Highline Community college.
I told Mike he has to go first on this one because I think I think I have a killer solution But I don't want mike to know what it is. I can't wait. I've been around to get this done. All right Here's the setup someone just said I need to round But guess what every time there's a category dollar round to the 4 otherwise if it's a crude 2.
So I'm going to go ahead. I just use an IF but it'll be inside the round so = ROUND.
Where's the number it's right there comma and number of digits while we need a 4 or 2 so it's simply IF this = and I'm going to click on this and then double click it and hit F9 because I'm such a bad typer Anytime that's equal to that, then what do I want.
The value if true.
Well that's a logical test that comes out true or false.
You know I think dollars be 2 and crude should be 4. I think we have it backwards, okay, suit that way, so value of true It's going to be 2 , otherwise it's 4. Now this formula assumes that there are only dollars and crude in the column. All right a close parenthesis and all.
The IF is doing is delivering one of two numbers to that argument.
Double click and send it down. Now. I'm going to cheat. I'm going to do it another way also You know we could do the same round But what if we had a table over here.
And we had dollar and crude actually maybe you do it down here just for the video and we had our 2 and 4 This example We'll use VLOOKUP inside the round but this would accommodate if you had many different categories that require different rounding.
So I'll just do the round of this comma VLOOKUP and the lookup value it's going to be that category , the lookup the table array and F4 comma column that's the second column we need to return the item from 2 and then comma False so I'm going to put 0 for an exact match that's the vlookup inside of numbers VLOOKUP and send it down and this one's has the decimals, but there's two ways to do. All right. I can't wait Wait a sec. I don't like that one because it's showing way too many decimals, so you know, I'm not sure about the person who sent this question. I don't think they really need it rounded.
I think they just want to display it rounded. I think they just want to display it rounded. I'm going to come here I'm on it. This is new you can't do this before excel 2007. I'm going to go to Home Conditional Formatting New Rule Use a formula to decide which cells to format and the formula is going to be = so I'm sitting right now in A2 So I'm going to look one cell to the right to me =B2 ="DOLLAR" If that's true when I click the Format check this out.
They added a number of formatting.
So number formatting I'm going to show two decimals for that particular row.
Click OK and then I'm going to take this whole column and kick it up to 4 decimals number 2 3 4 four click ok and then what happens is if the cell to the right of me is a dollar the Conditional Formatting kicks in and only gives me two decimal places.
Otherwise the default is I get 4 and it will display correctly.
"Conditional Formatting" and ofcourse the trick there is we're using a formula that is pointing one cell to the right in order to pull that off. Well what you think Oh the Number tab when did that come in in Conditional Formatting?
I think in excel 2007, it wasn't there in 2003 I know that.
And everyone wanted it.
Oh yes, it's I used it once in a book. I don't remember which book for If we had numbers that were like less than a thousand between a thousand a million and greater than a million I would show either comma k or comma comma m depending on how big a number was it was just on the fly.
Amazing right that's what's so great about these tools. We just learn amazing things oh i never thought.
I didn't think of until Mike starts recorder. Oh wait you go first. I got one All right, well hey I want to thank everyone for stopping by.
Will see you next time for another dueling excel podcast from MrExcel and Excel Is Fun.
