Using double unary --

[theta]

Hi

When should I be using this?

I normally throw it into my sumproduct when I get a zero result or an error, to force the ranges into giving a true/fale 1/0. Normally when there is text within a numeric range or something along those lines

SUMPRODUCT(--(PRODUCTYPE=PRDUCE)*--(LOCATION=THIS),--(COST))

Thoughts?

 

[cornflakegirl]

Try this page that explains really well how SUMPRODUCT works - it's got a great bit on the double unary about halfway down. http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

Hi

When should I be using this?

I normally throw it into my sumproduct when I get a zero result or an error, to force the ranges into giving a true/fale 1/0. Normally when there is text within a numeric range or something along those lines

SUMPRODUCT(--(PRODUCTYPE=PRDUCE)*--(LOCATION=THIS),--(COST))

Thoughts?

A coercer like -- is used...

(1) when you need to transform an array of TRUE/FALSE's into 1/0's;

(2) when you need to coerce a text-number into a true number.

By the way, do not mix * and -- for they both act as coercers. Thus:

SUMPRODUCT(--(PRODUCTYPE=PRDUCE),--(LOCATION=THIS),COST)

 

[RoryA]

Either use double unary, or use the multiplication, but not both. And don't use the -- on the data range, unless it's numbers stored as text.

 

[theta]

Perfect, any more pointers?

I have been using (TEST1)*(TEST2),(RESULT)

Only using the comma on the last method. Does this really matter

The only time I need to use anything else is the + for an OR method?

 

[cornflakegirl]

If you read xld's page that I suggested, he has a whole section on the different coercion methods. In summary, it doesn't really matter, but lots of people prefer the --.

 

[Aladin Akyurek]

Perfect, any more pointers?

I have been using (TEST1)*(TEST2),(RESULT)

Only using the comma on the last method. Does this really matter

The only time I need to use anything else is the + for an OR method?

Not sure what you mean by TEST1 and TEST2. If they are conditional expressions, we can have:

--(TEST1),--(TEST2),RESULT

(TEST1)+0,(TEST2)+0,RESULT

(TEST1)*(TEST2),RESULT

(TEST1)*(TEST2)*RESULT

OR has to be mapped sometimes on +, but, it can be, more often not,
rewritten as:

ISNUMBER(MATCH(Range,List,0))

Here more links...

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

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

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

And a first white paper on SumProduct:

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