You need $273 in cash. How many of each currency amount will you need? This Dueling Episode 1114 shows a few different ways to go.
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, I have Mike Girvin from ExcelIsFun.
Jim from YouTube sent in this mind-bending question!
$273, how do we do that in BILLS?
So that's 2 $100 bills, one 50, one 20, and so on, and so let's get rid of these hard-coded numbers and see if we can build, this is going to be two different formulas.
First formula, I know I'm going to use the INT function, =INT of this number 273.
I'm going to press F4 3 times to lock at the column A, divide it by this number up here in row 1, so I'll press F4 2 times, and I should get 2, beautiful.
OK, now I'm going to copy that formula over, yeah, there would be five 50’s in 273, but I don't want 273, I want to subtract out everything that I've already done before me.
So minus, and then a closing parenthesis, OK, what am I going to put in there?
I need to subtract 100*2, but, as I move that formula across, it’s going to need to subtract 100*2 and 50* whatever that number is, and 20* whatever that number is.
So I'm going to use SUMPRODUCT, and this is going to be a treat here!
It's going to be, we’re always want to start in B, so $B$1:B$1, alright, so we're going to multiply that range.
And then we always want to start in B again, $B2:B2, isn't that the weirdest combination of dollar signs you've ever seen?
And let's see, we need a closing parenthesis to finish off the SUMPRODUCT, 250, yeah, it looks right, let's copy it across, see how we do.
So, 200, 50, 70, yeah, 273, it works!
Let's try it, will copy this down, put a whole bunch of different numbers in and see what we get!
=3+, we just try out some different numbers here, and copy that down, Ctrl+V, check here, 300, so there's 3 $100 bills and nothing else, 303… Yeah, without doing a proof, looks like it works!
Alright, that's what I have, Mike, let's see what you can come up with!
Mike: Thanks MrExcel!
Hey, this is an amazing question, and I love the part of MrExcel’s formula where he used the SUMPRODUCT, I want to give you a little visual on that!
Remember, we had to multiply 100*4 when we got to this cell, but when we're over here, we need to do a 100*4 and 50*1 to get $450 to subtract from there.
Here's a formula, SUMPRODUCT, I locked the first one in that range, and I lock the first one in this range, this allows it to be an expandable range.
So when I click and drag it this way, you could see here, the range has expanded, over here, the range has expanded still further.
One other thing, I decided to put 100 in the cell, but it looks like there's a word “Bill”, Ctrl+1, that's custom number formatting, “Bill”, that way I can see “Oh, we're counting $100 bills.” Now, MrExcel used the INT function, I'm going to go ahead and use the QUOTIENT function, what's that?
Well, if you've watched these podcasts, you've seen the MOD function a lot, what does the MOD function and the QUOTIENT function do?
They're both related to division!
The MOD takes off 487/100 and gives you the remainder.
Quotient, it takes 487/100 and tells you how many, the integer part of it, so we get 4.
Now, QUOTIENT and INT deal with negative numbers differently, and I have some notes down here, if you want to download this workbook.
Ah, but for our money situation, where it's positive, it's going to make no difference.
I'm going to start off by doing QUOTIENT, and we need a numerator, that's the top, comma, and then the denominator, that's the bottom.
So INT and QUOTIENT will give us the same there, and then I'm going to go ahead and do QUOTIENT again.
And the numerator, remember, once I get past the first column, I need to add up all of the bills that have already been used.
So I'm going to take this, and lock it going to the side, I'm not going to copy it down like MrExcel did, and from that amount I'm going to subtract, and that's where that SUMPRODUCT comes in.
I'll take that, lock it for the column, comma, and the second array will be this one right here, and lock that right there.
By the way, what I did there is, once I type the first cell reference, I did Shift+: , let's try that again because that is a good trick.
I'm going to “boop” and then Shift+: while the dancing ants are still dancing right there, and it puts the second cell reference with the colon, and then I can highlight that one and lock it.
Alright, so now that is the numerator, comma, under the denominator, simply that right there, close parentheses, Ctrl+Enter and then copy it over.
Now I like to check it here, =SUMPRODUCT, as you saw, MrExcel’s faster doing math in his head than me.
I can't do that, I've done Excel so long, I forgot basic math, ah, but the SUMPRODUCT will check it for us!
I just go “Hey, all of those!” Comma, all of those, and it does help if you make that little noise, but there it is, it'll take those, multiply them, and then add them, and so there we get our check figure.
Now, notice we did 1-formula, 2-formula, sometimes I don't like to do that, in this situation, it works just fine, but if, for some reason, you didn't want to.
You could take this, and instead of starting at C1:C1, you could start here, and do that blank cell there and that blank cell there.
Now it will require that you had a blank cell there, but when I tried it, the SUMPRODUCT didn't handle it.
Guess what, the SUMPRODUCT isn’t versatile as SUM function when you enter it as an array, and SUM function as an array will handle this!
So I'm going to try =QUOTIENT, I'm going to take this right here, F4 it, and from it I'm going to subtract not to SUMPRODUCT but to SUM.
And I'm going to start right there, Shift+: to put that in, and then lock that first one, not comma but multiply, and get that, “boop”, right there, Shift+: and then I'm going to lock that one right there, close parentheses, ah!
And so that is the numerator for QUOTIENT, and then we'll just do our denominator starting right there, close parenthesis, ah!
SUM array multiplying arrays requires Ctrl+Shift+Enter, so you hold Ctrl+Shift and tap Enter, and then drag it over.
And then you can do the same thing with the SUMPRODUCT to check it there.
Alright, so we could use that one with the SUM, Ctrl+Shift+Enter, or QUOTIENT there and QUOTIENT there.
Alright, we'll see you next trick!
Bill: Hey alright Mike, that was cool, using QUOTIENT, love it!
Want to thank everyone for stopping by, we’ll see you next time for another Dueling Excel podcast from ExcelIsFun and MrExcel!
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, I have Mike Girvin from ExcelIsFun.
Jim from YouTube sent in this mind-bending question!
$273, how do we do that in BILLS?
So that's 2 $100 bills, one 50, one 20, and so on, and so let's get rid of these hard-coded numbers and see if we can build, this is going to be two different formulas.
First formula, I know I'm going to use the INT function, =INT of this number 273.
I'm going to press F4 3 times to lock at the column A, divide it by this number up here in row 1, so I'll press F4 2 times, and I should get 2, beautiful.
OK, now I'm going to copy that formula over, yeah, there would be five 50’s in 273, but I don't want 273, I want to subtract out everything that I've already done before me.
So minus, and then a closing parenthesis, OK, what am I going to put in there?
I need to subtract 100*2, but, as I move that formula across, it’s going to need to subtract 100*2 and 50* whatever that number is, and 20* whatever that number is.
So I'm going to use SUMPRODUCT, and this is going to be a treat here!
It's going to be, we’re always want to start in B, so $B$1:B$1, alright, so we're going to multiply that range.
And then we always want to start in B again, $B2:B2, isn't that the weirdest combination of dollar signs you've ever seen?
And let's see, we need a closing parenthesis to finish off the SUMPRODUCT, 250, yeah, it looks right, let's copy it across, see how we do.
So, 200, 50, 70, yeah, 273, it works!
Let's try it, will copy this down, put a whole bunch of different numbers in and see what we get!
=3+, we just try out some different numbers here, and copy that down, Ctrl+V, check here, 300, so there's 3 $100 bills and nothing else, 303… Yeah, without doing a proof, looks like it works!
Alright, that's what I have, Mike, let's see what you can come up with!
Mike: Thanks MrExcel!
Hey, this is an amazing question, and I love the part of MrExcel’s formula where he used the SUMPRODUCT, I want to give you a little visual on that!
Remember, we had to multiply 100*4 when we got to this cell, but when we're over here, we need to do a 100*4 and 50*1 to get $450 to subtract from there.
Here's a formula, SUMPRODUCT, I locked the first one in that range, and I lock the first one in this range, this allows it to be an expandable range.
So when I click and drag it this way, you could see here, the range has expanded, over here, the range has expanded still further.
One other thing, I decided to put 100 in the cell, but it looks like there's a word “Bill”, Ctrl+1, that's custom number formatting, “Bill”, that way I can see “Oh, we're counting $100 bills.” Now, MrExcel used the INT function, I'm going to go ahead and use the QUOTIENT function, what's that?
Well, if you've watched these podcasts, you've seen the MOD function a lot, what does the MOD function and the QUOTIENT function do?
They're both related to division!
The MOD takes off 487/100 and gives you the remainder.
Quotient, it takes 487/100 and tells you how many, the integer part of it, so we get 4.
Now, QUOTIENT and INT deal with negative numbers differently, and I have some notes down here, if you want to download this workbook.
Ah, but for our money situation, where it's positive, it's going to make no difference.
I'm going to start off by doing QUOTIENT, and we need a numerator, that's the top, comma, and then the denominator, that's the bottom.
So INT and QUOTIENT will give us the same there, and then I'm going to go ahead and do QUOTIENT again.
And the numerator, remember, once I get past the first column, I need to add up all of the bills that have already been used.
So I'm going to take this, and lock it going to the side, I'm not going to copy it down like MrExcel did, and from that amount I'm going to subtract, and that's where that SUMPRODUCT comes in.
I'll take that, lock it for the column, comma, and the second array will be this one right here, and lock that right there.
By the way, what I did there is, once I type the first cell reference, I did Shift+: , let's try that again because that is a good trick.
I'm going to “boop” and then Shift+: while the dancing ants are still dancing right there, and it puts the second cell reference with the colon, and then I can highlight that one and lock it.
Alright, so now that is the numerator, comma, under the denominator, simply that right there, close parentheses, Ctrl+Enter and then copy it over.
Now I like to check it here, =SUMPRODUCT, as you saw, MrExcel’s faster doing math in his head than me.
I can't do that, I've done Excel so long, I forgot basic math, ah, but the SUMPRODUCT will check it for us!
I just go “Hey, all of those!” Comma, all of those, and it does help if you make that little noise, but there it is, it'll take those, multiply them, and then add them, and so there we get our check figure.
Now, notice we did 1-formula, 2-formula, sometimes I don't like to do that, in this situation, it works just fine, but if, for some reason, you didn't want to.
You could take this, and instead of starting at C1:C1, you could start here, and do that blank cell there and that blank cell there.
Now it will require that you had a blank cell there, but when I tried it, the SUMPRODUCT didn't handle it.
Guess what, the SUMPRODUCT isn’t versatile as SUM function when you enter it as an array, and SUM function as an array will handle this!
So I'm going to try =QUOTIENT, I'm going to take this right here, F4 it, and from it I'm going to subtract not to SUMPRODUCT but to SUM.
And I'm going to start right there, Shift+: to put that in, and then lock that first one, not comma but multiply, and get that, “boop”, right there, Shift+: and then I'm going to lock that one right there, close parentheses, ah!
And so that is the numerator for QUOTIENT, and then we'll just do our denominator starting right there, close parenthesis, ah!
SUM array multiplying arrays requires Ctrl+Shift+Enter, so you hold Ctrl+Shift and tap Enter, and then drag it over.
And then you can do the same thing with the SUMPRODUCT to check it there.
Alright, so we could use that one with the SUM, Ctrl+Shift+Enter, or QUOTIENT there and QUOTIENT there.
Alright, we'll see you next trick!
Bill: Hey alright Mike, that was cool, using QUOTIENT, love it!
Want to thank everyone for stopping by, we’ll see you next time for another Dueling Excel podcast from ExcelIsFun and MrExcel!
