exceltoxl.com

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.

Download Working File.xlsx

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:

Miracles Hidden Inside Tables

Create Symbols Using Keyboard Shortcuts

Count Coloured Cells (simple vba)

List FileNames in a Folder (no vba)

Count Worksheets in a Workbook

Thanks,