Sumproduct(N and --

  • Thread starter Thread starter BobS
  • Start date Start date
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.
 
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
 
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).
 
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).
 
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.
 
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
 
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))
 
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))
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct Problem 1
Sumproduct gives #value 6
Arrayformulas with either/or nested to sumproduct if eitherconditions are true 13
Sumproduct, but errors in column 6
SUMProduct Part II 5
How to make SUMPRODUCT work over range that includes text? 4
#Ref on Sumproduct 0
Sumproduct 6

Back
Top