Lesson in coerced sumproduct please

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
"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. :LOL: 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?
 
Upvote 0
PaddyD said:
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?
 
Upvote 0
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*
 
Upvote 0

Forum statistics

Threads
1,218,538
Messages
6,143,078
Members
450,461
Latest member
Bosavon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top