Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

What is – – (double negative or dash) in Excel?

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.

Some time changing track is a smart way to reach destination

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.

 

As we understand that many Excel Information Functions like ISBLANK, ISERR, ISLOGICAL are expected to return a proper Boolean value i.e., True or FALSE

Let’s suppose we simply want to find if a value is an error or not using ISERR function. The ISERR function checks if a specified cell has error value ‘except #N/A’.

ISERR check error and returns boolean value.

Now let’s check out, How double negative will convert Boolean to its numeric value?

Automatically coerces boolean values to numeric values

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.

F9 Return boolean value (TRUE & FALSE)
F9 Return boolean value including '3 TRUE'
Double Hyphen returns Numeric value (1,0)
Double Hyphen returns Numeric value (1,0)

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).

In place of SUMPRODUCT we can also use SUM function as Array formula, with Ctrl+Shift+Enter (CSE).

{=SUM(–ISTEXT(B5:B11))}          ‘With Array formula {}

The result is same, the only point to highlight is SUMPRODUCT function can handle Array but SUM function can not.

Alternative to Double Hyphen

In case you are not comfortable with using double hyphen we can easily use other formula or tricks.

The N function returns a code number by converting specific reference based on a set of rule. I.e., =N(B6) will return 43093, B6 refers to ’24-12-2017′ (date to numeric value).

=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.