If you are searching double negative (- -), I am sure you must have fumble with an Excel formula that is including such double negative operator. You can also say it as ‘double not’ or ‘coerce’.
Excel is not very compatible with using Text for mathematical operation.
The purpose unary operator (-) twice or double negative or dash is for converting the previous Boolean result (TRUE and FALSE) of an Excel formula back to the integers (1 and 0), respectively.
Download Your Example Excel File
- While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.
Now let’s check out, How double negative will convert Boolean to its numeric value?
Application Double Negative in Formula
Now we will look into an Example Formula to better understand the application of double negative.
In this Example, you create a formula to count number of text in the data range.
The first step is to evaluate each cell for Text; and return TRUE if Text and FALSE if not. And, second step is to count number of TRUE.
After implementing the function we have three ‘TRUE’, it is time to count them (Second Step) using a formula.
SUMPRODUCT function count in case it’s only one array but as I mention Excel can not do mathematical operation with texts like ‘TRUE’ and ‘FALSE’.
Without double dash the formula would return ‘Zero’.
=SUMPRODUCT(ISTEXT(B5:B11)) ‘Return Zero
SUMPRODUCT function returns the count of TRUE that is 1 (after – -).
=SUMPRODUCT(–ISTEXT(B5:B11)) ‘Convert to numeric
We can also debug or evaluate each formula using F9 shortcut. Just select the formula and press F9.
If you will compare both image above you will understand that only difference is double hyphen that result numerical variable with one binary digit (1,0).
Alternative to Double Hyphen
In case you are not comfortable with using double hyphen we can easily use other formula or tricks.
=SUMPRODUCT(N(ISTEXT(B5:B11))) ‘Code of TRUE and FALSE is 1 and 0.
We can also use below tricks that convert a Text to Number by adding or multiplying a boolean:
=SUMPRODUCT(*1(ISTEXT(B5:B11))) ‘just multiply with 1.
=SUMPRODUCT(0+(ISTEXT(B5:B11))) ‘just add Zero that convert
- Each hyphen perform as a negation, if you will use only one hyphen (-) then result will be -1 rather than 1 with double hyphen.