We don't call it 'Dueling Excel' for No Reason!
And at some point one has to stop and ask, "How can two guys possibly disagree about the best way to Round Invoices?"
Well... leave it to Mike and Bill because they can - and do. Today's Duel is figuring out the best way to Round Invoices in Episode #1483
Dueling Excel Podcast #92...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
And at some point one has to stop and ask, "How can two guys possibly disagree about the best way to Round Invoices?"
Well... leave it to Mike and Bill because they can - and do. Today's Duel is figuring out the best way to Round Invoices in Episode #1483
Dueling Excel Podcast #92...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill Jelen: Hey, welcome back. This is another duelling podcast. I’m Bill Jelen from MrExcel.
I’ll be joined by Mike Girvin from Excel Is Fun.
This is our Episode 92 – Rounding Invoices.
All right.
So someone sent this question in and they have a nice little spreadsheet here with the Price Each, the Quantity.
And they formatted it, put a Subtotal in, 14.54.
But they said, “Hey, if I just come over here, and type what I see – 5.03, 1.2, 2.37, 5.95, and total it up, I get a different answer.” What is up with that?
All right.
Well hey, this is formatting as a facade.
As Mike always says, in his slang, Excel dragon’s book.
Those numbers aren't what we see.
They're actually stored as these numbers, all right.
And they went through and changed the formatting to make it only have two decimal places, but those third, fourth, fifth, sixth decimal place, they're still there, all right.
So we add up, you know, these two numbers were rounded up when we formatted them, but it's just the formatting.
That's just what it's printing, right.
It's still storing those other numbers hence, the smaller number here.
So my solution to this, my solution to this, well I'm tempted to go into Excel options and turn on Precision as Displayed, but I'm not sure how that's going to work with formulas, so I'm just going to come back here to the formula.
I'm going to say, “Hey, look.
I don't expect anyone to actually send us 5.025 cents.” How are they going to send me half a cent?
I really can’t expect them to send me 5.03.
So let’s just change this here.
We’re going to say, =IF(A2= " ", " ",ROUND(C2*B2,2)).
The round of that, comma, 1.
That will round it to two decimal places.
And I think I hit Ctrl Enter there.
Yes, I did to put that all the way down.
So now we have 14.55 and 14.55, and it works that way.
Mike, let's see what you have.
Mike Girvin: Thanks, MrExcel.
Hey, the old standard invoice is not showing the correct total based on rounding error, and I love this.
This is the way to go.
Now, this is actually how I teach it in my Business Math and Finance classes.
You always go to the actual calculation that needed rounding, and round it there.
Now, why do you have to use the round function?
Well anytime you're multiplying or dividing decimals, you're required to round, meaning we have money, and you're using your formula result in a subsequent formula.
Those three conditions, then you have to use the round function.
Now, I like this individual rounding calculation here, but there is another way we could do this.
I'm going to come over to this other sheet.
And here's the total, right.
These are not rounded.
Well, what's the problem?
This isn't showing 14.55.
So I'm simply going to put this in edit mode, and round this.
So round =SUM(ROUND(D2:D5,2)).
Now round function, that number argument’s expecting a single number, and I'm giving it four, comma two, to the penny.
So because I've given it four numbers instead of two, this makes an array formula, so you have to enter it with a special keystroke – Ctrl Shift.
You hold those down, and tap Enter.
{SUM(ROUND(D3:D5,2))}.
That's Excel telling you that it understood, that this was an array formula.
Now, if you don't use Ctrl Shift Enter, you get a value error.
That value of error says, you forgot Ctrl Shift Enter.
Now in this particular situation, you know, if you go research this on Google, or something, or Microsoft Help.
They usually say, use the sum, the round, and Ctrl Shift Enter, but forget that.
I'm going to use SUMPRODUCT.
=SUMPRODUCT(ROUND(D2:D5,2)).
SUMPRODUCT is actually programmed to take arrays, multiply them, and then add.
Product, multiply, some add, but I'm only going to give it only a single array so it's not going to do any multiplying.
It'll just do the sum part add.
And the cool thing about SUMPRODUCT like lookup - I like lookup, function and index.
Those 3 functions including SUMPRODUCT, do not require Ctrl Shift Enter.
They can handle arrays.
It says array right there.
So I just hit Enter.
And there we have it.
All right.
I’ll throw you back to MrExcel.
I’ll be joined by Mike Girvin from Excel Is Fun.
This is our Episode 92 – Rounding Invoices.
All right.
So someone sent this question in and they have a nice little spreadsheet here with the Price Each, the Quantity.
And they formatted it, put a Subtotal in, 14.54.
But they said, “Hey, if I just come over here, and type what I see – 5.03, 1.2, 2.37, 5.95, and total it up, I get a different answer.” What is up with that?
All right.
Well hey, this is formatting as a facade.
As Mike always says, in his slang, Excel dragon’s book.
Those numbers aren't what we see.
They're actually stored as these numbers, all right.
And they went through and changed the formatting to make it only have two decimal places, but those third, fourth, fifth, sixth decimal place, they're still there, all right.
So we add up, you know, these two numbers were rounded up when we formatted them, but it's just the formatting.
That's just what it's printing, right.
It's still storing those other numbers hence, the smaller number here.
So my solution to this, my solution to this, well I'm tempted to go into Excel options and turn on Precision as Displayed, but I'm not sure how that's going to work with formulas, so I'm just going to come back here to the formula.
I'm going to say, “Hey, look.
I don't expect anyone to actually send us 5.025 cents.” How are they going to send me half a cent?
I really can’t expect them to send me 5.03.
So let’s just change this here.
We’re going to say, =IF(A2= " ", " ",ROUND(C2*B2,2)).
The round of that, comma, 1.
That will round it to two decimal places.
And I think I hit Ctrl Enter there.
Yes, I did to put that all the way down.
So now we have 14.55 and 14.55, and it works that way.
Mike, let's see what you have.
Mike Girvin: Thanks, MrExcel.
Hey, the old standard invoice is not showing the correct total based on rounding error, and I love this.
This is the way to go.
Now, this is actually how I teach it in my Business Math and Finance classes.
You always go to the actual calculation that needed rounding, and round it there.
Now, why do you have to use the round function?
Well anytime you're multiplying or dividing decimals, you're required to round, meaning we have money, and you're using your formula result in a subsequent formula.
Those three conditions, then you have to use the round function.
Now, I like this individual rounding calculation here, but there is another way we could do this.
I'm going to come over to this other sheet.
And here's the total, right.
These are not rounded.
Well, what's the problem?
This isn't showing 14.55.
So I'm simply going to put this in edit mode, and round this.
So round =SUM(ROUND(D2:D5,2)).
Now round function, that number argument’s expecting a single number, and I'm giving it four, comma two, to the penny.
So because I've given it four numbers instead of two, this makes an array formula, so you have to enter it with a special keystroke – Ctrl Shift.
You hold those down, and tap Enter.
{SUM(ROUND(D3:D5,2))}.
That's Excel telling you that it understood, that this was an array formula.
Now, if you don't use Ctrl Shift Enter, you get a value error.
That value of error says, you forgot Ctrl Shift Enter.
Now in this particular situation, you know, if you go research this on Google, or something, or Microsoft Help.
They usually say, use the sum, the round, and Ctrl Shift Enter, but forget that.
I'm going to use SUMPRODUCT.
=SUMPRODUCT(ROUND(D2:D5,2)).
SUMPRODUCT is actually programmed to take arrays, multiply them, and then add.
Product, multiply, some add, but I'm only going to give it only a single array so it's not going to do any multiplying.
It'll just do the sum part add.
And the cool thing about SUMPRODUCT like lookup - I like lookup, function and index.
Those 3 functions including SUMPRODUCT, do not require Ctrl Shift Enter.
They can handle arrays.
It says array right there.
So I just hit Enter.
And there we have it.
All right.
I’ll throw you back to MrExcel.
