MrExcel's Learn Excel #899 - Minus Minus

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jan 5, 2009.
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.
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top