Lesson in coerced sumproduct please

[babycody]

I have become a big fan of sumproduct. However there is something that I would like explained to me either through a post or a link. Why is it neccesary in some cases to coerce the ranges, but in other instances it isn't? I understand that coercing changes true to 1 and false to 0. Why would it make a difference to use one or the other. Doesn't TRUE TRUE get the same results as 1*1? Thanks all.

 

[PaddyD]

somthing that might help here:

http://www.mrexcel.com/board2/viewt...ays=0&postorder=asc&highlight=coerce&start=10

 

[PaddyD]

an elaboration:

the syntax for sumproduct is:

SUMPRODUCT(array1,array2,array3, ...)

...but you will also notice another idiom in use:

sumproduct(array1*array2*array3)

...in the former, each individual array needs the logical to numeric coersion:

sumproduct(--(array1-condition),--(array2=condition),--(array3=condition))

In the latter, the multiplication does it anlong the way.

 

[babycody]

"It is in the nature of excel, and some other languages, that performing math operations on logicals will coerce them into numerics"

I am almost sure that you are giving away the secret in this statement. What is a logical? I still can't say that I see why you have to use it in some cases but not in others. You'll have to give this to me in the "Coerced Sumproduct for Dummies" version I'm afraid.

Here is where I show my true ignorance: : -> Does it have to do with using text instead of a numeric value in the ranges?

 

[babycody]

an elaboration:

the syntax for sumproduct is:

SUMPRODUCT(array1,array2,array3, ...)

...but you will also notice another idiom in use:

sumproduct(array1*array2*array3)

...in the former, each individual array needs the logical to numeric coersion:

sumproduct(--(array1-condition),--(array2=condition),--(array3=condition))

In the latter, the multiplication does it anlong the way.

So it all depends on the syntax I use for sumproduct to seperate the arrays and nothing more?

 

[Brian from Maui]

From what little I know, there is a difference between a comma syntax and asterisk syntax. When the ranges consist of a mix of vectors and matrices, you have to switch to the "star syntax". That means: A SumProduct formula with the "star syntax" instead of the native comma syntax or to an array formula.

The -- is just a coercer, which can be replaced by +0 or 1*