« Custom Markers in Charts | Get Distinct Count with Formulas » |
Hi there,
In this post, you will be learning why and when to use double minus -- in the formulas. Firstly, have you ever seen double minus symbol in formulas? If yes, have you ever thought why to use such minus symbols. If you know, please skip this post for now, and post your experience in the blow comments section. If you are new to it, then Here we go:
WHEN TO USE --?
Double minus ( -- ) will be used when the formula is delivering logical values rather than the numerical values.
WHY TO USE --?
...
Double minus can be used because as we learnt in mathematics, minus multiplies with minus gives plus, same theory applies here. This method will not impact our calculations in the formula. This is as simple as multiplying zero with any value (100*0 = 0) gives 0.
This feature here is used to replace the logical values to the numerical values to display the desired results, especially in array formulas.
CAN YOU BRIEF?
Yes, I will.
Example:
I have few courses running on my blogs, as well as offline. I want to know how many enrollments took place in each course?
There are many ways doing this. But, I am taking a scenario where I can demonstrate how and why to use (--) minus.
I have used SUMPRODUCT function (this function strongly supports arrays) to extract the number of enrollments to each course. Here is my Course_Database
I have used SUMPRODUCT with out minus symbol and here is the result
Very sad, it didn't help me. WHY is that?, Let's select the course range
Oh! see here!!!
It is showing logical values, rather than numerical values. What a Pathetic Yar?
How about C4:C10 (purchased column), Oh! it is showing values as usual.
The combination of logical values and numerical values which will not fetch numerical values. Ok!, now I got it why this formula is fetching zero (0) as a result.
Let me put -- in the SUMPRODUCT formula (where ever the range is fetching logical values) - WoW! I got a result. Awesome!!!
What -- does here , it converted logical values to numerical values,
Yes, this will fetch the results as desired.
That's it. Magic Works..!
DO YOU USE --?
If you have different experiences using double minus, please do teach us using below comments section.
If you like this post, please do share it on your social networking sites by clicking on the icons placed around the blog.
If you want to join our family to receive such updates directly to your email, JOIN US.
SOME IMPORTANT LINKS:
Create Symbols Using Keyboard Shortcuts
Count Coloured Cells (simple vba)
List FileNames in a Folder (no vba)
Count Worksheets in a Workbook
Thanks,
You must be logged in to see the comments. Log in now!
If you have no account yet, you can register now! (It only takes a few seconds)
Recent comments