Kim asks about the minus minus in certain formulas. In Episode 899, I will break one of those formulas down and show exactly why people use the minus minus.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today, we have a question sent in by Kim.
Kim sent us in on the 10th Anniversary webinar, said, "When I see a formula that has the minus minus, what are you trying to do there?" And basically, I'm going to set up a simple situation.
We have a data set here-- Region, Product, and Sales-- I want to figure out the Sales for East, ABC.
Now, in Excel 2007, we can use the new SUMIFS; but in the old versions of Excel, we were stuck with SUMPRODUCT.
Basically, what this is doing, let's say that we take a look at everything from A2 to A11, and see if it's equal to that word East up there in E1-- press Ctrl+Shift+Enter and you see that I get a series of TRUEs and FALSEs.
And if I view the same thing over here for the ABCs-- so we checked B2 to B11, see if it's equal to F1, Ctrl+Shift+Enter, I get a series of TRUEs and FALSEs.
Now, in the old Boolean logic, you could take TRUE*TRUE*a number, and it would come up with the number; and then TRUE*FALSE, or FALSE*FALSE*the number was 0; but for some weird reason, Excel simply cannot deal with that.
When we use SUMPRODUCT and multiply those three ranges together, we're getting nothing.
So the reason that people use the minus minus is that that is a clever way to coerce Excel to change those trues and falses back to 0s and 1s.
Ctrl+Shift+Enter, we get a series of 0s and 1s.
The TRUEs are 1, the FALSEs are 0.
I'll do that again here, minus minus, and now you see that the SUMPRODUCT starts to work.
And so when people need to do conditional calculations-- let's see what the West XYZ is-- you'll see them using SUMPRODUCT with minus minus.
Now, the other way to go is actually to not separate those ranges by commas, but to use a multiplication sign, and that also will force Excel to coerce those TRUEs and FALSEs into 0s and 1s.
So either method seems to be on the MrExcel board.
They tend to like to use the minus minus; personally, I use the multiply; but either one.
Kim, thanks for sending that question in, thanks for attending the webinar, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today, we have a question sent in by Kim.
Kim sent us in on the 10th Anniversary webinar, said, "When I see a formula that has the minus minus, what are you trying to do there?" And basically, I'm going to set up a simple situation.
We have a data set here-- Region, Product, and Sales-- I want to figure out the Sales for East, ABC.
Now, in Excel 2007, we can use the new SUMIFS; but in the old versions of Excel, we were stuck with SUMPRODUCT.
Basically, what this is doing, let's say that we take a look at everything from A2 to A11, and see if it's equal to that word East up there in E1-- press Ctrl+Shift+Enter and you see that I get a series of TRUEs and FALSEs.
And if I view the same thing over here for the ABCs-- so we checked B2 to B11, see if it's equal to F1, Ctrl+Shift+Enter, I get a series of TRUEs and FALSEs.
Now, in the old Boolean logic, you could take TRUE*TRUE*a number, and it would come up with the number; and then TRUE*FALSE, or FALSE*FALSE*the number was 0; but for some weird reason, Excel simply cannot deal with that.
When we use SUMPRODUCT and multiply those three ranges together, we're getting nothing.
So the reason that people use the minus minus is that that is a clever way to coerce Excel to change those trues and falses back to 0s and 1s.
Ctrl+Shift+Enter, we get a series of 0s and 1s.
The TRUEs are 1, the FALSEs are 0.
I'll do that again here, minus minus, and now you see that the SUMPRODUCT starts to work.
And so when people need to do conditional calculations-- let's see what the West XYZ is-- you'll see them using SUMPRODUCT with minus minus.
Now, the other way to go is actually to not separate those ranges by commas, but to use a multiplication sign, and that also will force Excel to coerce those TRUEs and FALSEs into 0s and 1s.
So either method seems to be on the MrExcel board.
They tend to like to use the minus minus; personally, I use the multiply; but either one.
Kim, thanks for sending that question in, thanks for attending the webinar, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
