usage of (--) or (-)

The use of -- in SUMPRODUCT formulas has nothing to do with text or dates. It is used to coerce the returned TRUE/FALSE values from the conditions into 1 and 0 respectively. (Ordinarily I would point you to this page for more details, but it seems that it's too popular and has exceeded the bandwidth cap for the month (again! )

In Excel worksheets we have a few different data types including:

• Number
• Text
• Logical
• Error

Sometimes you will see these data types referred to using slightly different terms, for example Logical data types can also be referred to as Boolean data types.

Some functions can only successfully process certain data types. This is determined by how the function is registered. In the case of SUMPRODUCT(), it works really well with Numbers, but it's not too hot on Logicals.

The examples you will have seen with the -- will most probably be when some Logicals are being coerced into Numbers so that SUMPRODUCT() can process them.

If the link Rory gave you to Bob's site isn't working soon let me know and I will give you some links to other similar material. You can also search the forum for terms such as "negation", "coercion" and "double unary minus" and you should get some useful hits.

Hope that helps...

Thanx Rorya and colin for your reply.

Colin not able to open the page request your help with other links

i do not really understand why -- is preferred to the *
as the above mentioned page points out (which is down right now), * is superior to -- because it can process conditions in horizontal alignment on which the unary operation fails imho

vinod9111 said:

Thanx Rorya and colin for your reply.

Colin not able to open the page request your help with other links

Click to expand...

Okay, that page still appears to be down. You can try this link. It explains a bit about negation in #5 and at the end in #12 it has a links section to other array formula tutorials.

Just as a very quick & dirty example:



The * method here fails as it tries to multiply by a text value in the final column, the double unary method will account for only numerical values.

The * method has advantages over unary where dimensions are not of a consistent size ...

P.S. Don't get Colin started on single vs double unary operators

Re: the efficiencies etc... the links provided should provide ample info

Also:

http://www.mrexcel.com/forum/showthread.php?t=202204

http://www.mrexcel.com/forum/showthread.php?t=70547

http://www.mrexcel.com/forum/showthread.php?p=1671486

may be a little late...but still
here's a cached version of the webpage

http://web.archive.org/web/20080111121316/http://www.xldynamic.com/source/xld.SUMPRODUCT.html

DonkeyOte said:

The * method has advantages over unary where dimensions are not of a consistent size ...

Click to expand...

Hi Luke, nice example, I see that there is a clear error-trapping difference between the 2 types of SUMPRODUCT methods.

I don't quite understand what you meant by your quoute though, could you please calrify with another example?