• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Usage of 2 hyphens in formula

AK

New Member
Why and how are 2 consecutive hyphens used in an Excel formula?


e.g. what is the meaning of =sumproduct(--(a1:a4="x"),(b1:b4))
 
AK

Sumproduct works on multiplying each of the values in brackets by the next set of data in brackets from the same position and then adding it all up.


In your case it evaluates the (a1:a4="x") for each value in the range a1:a4

the value in each case will be True or false depending on the values in A1:A4


True or False don't have a stored value in Excel ie: 1 or 0

so to convert them people use a variety of techniques


Multiplying a True/False by 1 or in this case -- converts the True/False to either a 1 or 0


in your case --(a1:a4="x") is equivalent to -1*-1*(a1:a4="x")

I prefer a simple 1*(a1:a4="x") as its easier to read
 
Back
Top