Sumproduct(N and --

B

BobS

I use array formula quit often and was wondering if someone could please
explain the use of sumproduct(N and sumproduct(-- versus just sumproduct(.
I've seen the N and -- quit a bit in newsgroup responses, but am not clear
on their function. Thanks.
 
R

Ron Coderre

Here's an explanation of the Double Minus Sign (--)

When you use a Boolean formula (a formula that returns TRUE or FALSE), Excel
returns those values. If the formula returns "numeric text", Excel will
treat it AS text. To coerce the conversion from Boolean to Numeric, or from
"numeric text" to Numeric, you need to apply an arithmetic operator. The
generally accepted convention is to use a double minus sign (--).

It works this way:
The negative of a value reverses the sign.
The negative of that value restores the sign.

Example:
RIGHT("W1000",4) returns with the *word* "1000"
-RIGHT("W1000",4) converts "1000" to the number -1000
--RIGHT("W1000",4) converts the negative number to 1000

In the case of boolean values, the dbl-neg converts TRUE and FALSE to 1 and
0, respectively.

You could achieve the same results by multiplying a value by 1, but the
dbl-neg indicates to knowledgable users that a "type conversion" is being
effected.


I'm not sure what you mean by SUMPRODUCT(N. Can you give an example?

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
H

Harlan Grove

BobS wrote...
I use array formula quit often and was wondering if someone could please
explain the use of sumproduct(N and sumproduct(-- versus just sumproduct(.
I've seen the N and -- quit a bit in newsgroup responses, but am not clear
on their function. Thanks.
Click to expand...

Both coerce boolean values to numeric values, TRUE to 1 and FALSE to 0.
This is necessary because SUMPRODUCT skips anything other than numbers.
For example,

=SUMPRODUCT({1;2;3;4}<=3,{1;10;100;1000})

returns 0 because this is equivalent to

=SUMPRODUCT({FALSE;FALSE;TRUE;TRUE},{1;10;100;1000})

and SUMPRODUCT skips all the items in the first argument. However,

=SUMPRODUCT(--({1;2;3;4}<=3),{1;10;100;1000})

returns 1100 because this is equivalent to

=SUMPRODUCT({0;0;1;1},{1;10;100;1000})

N({1;2;3;4}<=3) returns the same thing as --({1;2;3;4}<=3), but N
involves a function call, and that often bumps up against Excel limit
of 7 nested function calls. Also, N's semantics when passed array
arguments is strictly speaking undocumented, so it could change in
future (but that's unlikely).
 
E

Epinn

Harlan,

I assume you meant >=3 (greater than or equal to 3) instead of <=3 in your example.

=SUMPRODUCT(--({1;2;3;4}>=3),{1;10;100;1000}) returns 1100

In case anyone is interested, I just want to say that the above formula is equivalent to

=SUMPRODUCT(({1;2;3;4}>=3)*{1;10;100;1000})

Sorry, Bob P., I know your preference is double unary and comma. Wonder if Harlan has a preference.

Epinn


Harlan Grove said:
I use array formula quit often and was wondering if someone could please
explain the use of sumproduct(N and sumproduct(-- versus just sumproduct(.
I've seen the N and -- quit a bit in newsgroup responses, but am not clear
on their function. Thanks.
Click to expand...

Both coerce boolean values to numeric values, TRUE to 1 and FALSE to 0.
This is necessary because SUMPRODUCT skips anything other than numbers.
For example,

=SUMPRODUCT({1;2;3;4}<=3,{1;10;100;1000})

returns 0 because this is equivalent to

=SUMPRODUCT({FALSE;FALSE;TRUE;TRUE},{1;10;100;1000})

and SUMPRODUCT skips all the items in the first argument. However,

=SUMPRODUCT(--({1;2;3;4}<=3),{1;10;100;1000})

returns 1100 because this is equivalent to

=SUMPRODUCT({0;0;1;1},{1;10;100;1000})

N({1;2;3;4}<=3) returns the same thing as --({1;2;3;4}<=3), but N
involves a function call, and that often bumps up against Excel limit
of 7 nested function calls. Also, N's semantics when passed array
arguments is strictly speaking undocumented, so it could change in
future (but that's unlikely).
 
B

Biff

That having been said, (though for the life
of me I can't remember any examples), I have
also had cases where only x*x
would work as opposed to double unary,
Click to expand...

Comparing a one dimensional array to a two dimensional array:

=SUMPRODUCT(--(A1:A5="x"),--(B1:C5="y"))
=SUMPRODUCT((A1:A5="x")*(B1:C5="y"))

The double unary version fails (#VALUE!)

Biff
 
R

Roger Govier

Equally, if all the arrays are not in the same plane, the double unary
version fails, where the "*" version works

=SUMPRODUCT((A2:A4="b")*(B1:E1="Dept2")*(B2:E4))

=SUMPRODUCT(--(A2:A4="b"),--(B1:E1="Dept2"),--(B2:E4))
 
E

Epinn

Thank you all for the wonderful examples, especially Roger's that includes row and column.

When I started to learn SUMPRODUCT, I compared between double unary/comma and * and I found that * worked more times than double unary. Hence, I "prefer" to use *.

However, if I remember correctly, I was told that * is implied by the word "product" in SUMPRODUCT, so comma is preferred. I'll let Bob P. clarify.

All in all, the important thing is that when one operator doesn't work, I should try the others.

Hope the original poster doesn't feel distracted when I started this interesting discussion by mentioning the word "preference."

In case BobS is interested, here is another link with good info. http://mcgimpsey.com/excel/formulae/doubleneg.html



While I have got the experts' attention, I am going to start my own thread on SUMPRODUCT and CSE.



Epinn





Equally, if all the arrays are not in the same plane, the double unary
version fails, where the "*" version works

=SUMPRODUCT((A2:A4="b")*(B1:E1="Dept2")*(B2:E4))

=SUMPRODUCT(--(A2:A4="b"),--(B1:E1="Dept2"),--(B2:E4))
 
E

Epinn

Experts,

I have a question and I am starting my own thread. The subject is SUMPRODUCT- double unary vs. *

Please help.

Epinn

Not going to try and answer for Harlan, but it's not so much a preference as
the right syntax for the job.

With the following data in A1:B5

abc def
1 2
3 4
2 3
4 5

Try each of the following formulas:-

=SUMPRODUCT(A1:A5*B1:B5)
=SUMPRODUCT(A1:A5,B1:B5)

First one fails, second one doesn't.

If you have text in any of your ranges (albeit even just the headers), then
the x*x syntax will fall over. That having been said, (though for the life
of me I can't remember any examples), I have also had cases where only x*x
would work as opposed to double unary, so i wouldn't say it's just a case of
preference.
 
Top