The trick of ––

You read it right. Its going to be a quick post but at the end of it, you would be glad that you read it.

Trick1: Text to Number

 Consider this
=ISNUMBER(LEFT(12345,1)) – this returns FALSE as this simplifies to =ISNUMBER(“1”)

However, =ISNUMBER(––LEFT(12345,1)) returns TRUE. The two minus signs in conjunction “––” converts text “1” to number 1

So, also =––”1″ equals numeric 1

Trick2: Boolean {TRUE, FALSE} to numeric equivalent {1,0}

Consider,

{=SUM({1,2,3,0,5}<>0)} This is an array formula which reduces to

=SUM({TRUE, TRUE, TRUE, FALSE, TRUE}) and returns a 0

However,

{=SUM(––({1,2,3,0,5}<>0))} correctly puts a 1 for every TRUE and 0 for every FALSE and returns a 4

Check this,

Key in =(1=1) – this returns a TRUE

Now =––(1=1) returns a 1

Before you ask, you will not obtain the same result if you replace –– with +

You may keep this in mind and find a use of it someday 🙂

Read Full Post »