I don't understand why sometimes I have to use "--" before an argument in
the SUMPRODUCT fuction, and sometimes a "*".

Can anyone point me to a resource that will explain why and when I have to
use these instead of just commas, so that I don't have to post any more
questions about it!

Thanks

M

Bob Philips has a great page on SUMPRODUCT and double unaries '--'

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Peter A

"Michelle" wrote:

I don't understand why sometimes I have to use "--" before an argument in
the SUMPRODUCT fuction, and sometimes a "*".

Can anyone point me to a resource that will explain why and when I have to
use these instead of just commas, so that I don't have to post any more
questions about it!

Thanks

M

Double unary minus:
a.. http://www.mcgimpsey.com/excel/formulae/doubleneg.html
b.. http://xldynamic.com/source/xld.SUMPRODUCT.html

--
David Biddulph


"Michelle" wrote in message
...
I don't understand why sometimes I have to use "--" before an argument in
the SUMPRODUCT fuction, and sometimes a "*".

Can anyone point me to a resource that will explain why and when I have to
use these instead of just commas, so that I don't have to post any more
questions about it!

Thanks

M

Here's another explanation, from John McGimpsey's site:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Hope this helps.

Pete

On Oct 1, 2:32*pm, "Michelle" wrote:
I don't understand why sometimes I have to use "--" before an argument in
the SUMPRODUCT fuction, and sometimes a "*".

Can anyone point me to a resource that will explain why and when I have to
use these instead of just commas, so that I don't have to post any more
questions about it!

Thanks

M

"Billy Liddel" wrote in message
...
Bob Philips has a great page on SUMPRODUCT and double unaries '--'

He also has 2 ls in his surname, Welsh origins not Dutch <bg

Bob Philips has a great page on SUMPRODUCT and double unaries '--'

He also has 2 ls in his surname, Welsh origins not Dutch <bg
Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault" ))"]
to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]
and judge yourself.

Regards,
Bernd

Bernd P wrote:
Bob Philips has a great page on SUMPRODUCT and double unaries '--'
He also has 2 ls in his surname, Welsh origins not Dutch <bg
Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault" ))"]


Both ranges are the same and since the same cell can't equal both "Ford" and
"Renault", this will produce the expected result.


to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]


=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100=”YES”)),C1:C100)

Two distinct ranges. Since A1=-3 and B1="Yes" is possible, it is a different
problem requiring a more complex solution.


and judge yourself.



Both solutions appear to be valid, but one is more robust.


Regards,
Bernd

Thanks Guys, it genuinely is all clear now.

Brilliant

M

"Billy Liddel" wrote in message
...
Bob Philips has a great page on SUMPRODUCT and double unaries '--'

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Peter A

"Michelle" wrote:

I don't understand why sometimes I have to use "--" before an argument in
the SUMPRODUCT fuction, and sometimes a "*".

Can anyone point me to a resource that will explain why and when I have
to
use these instead of just commas, so that I don't have to post any more
questions about it!

Thanks

M

=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100=”YES”)),C1:C100)

Also, in this application the double unary is redundant. Adding the 2 arrays
will coerce the Boolean to numeric:

=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100=”YES”)),C1:C 100)

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Bernd P wrote:
Bob Philips has a great page on SUMPRODUCT and double unaries '--'
He also has 2 ls in his surname, Welsh origins not Dutch <bg
Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault" ))"]


Both ranges are the same and since the same cell can't equal both "Ford"
and "Renault", this will produce the expected result.


to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]


=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100=”YES”)),C1:C100)

Two distinct ranges. Since A1=-3 and B1="Yes" is possible, it is a
different problem requiring a more complex solution.


and judge yourself.



Both solutions appear to be valid, but one is more robust.


Regards,
Bernd

"T. Valko" wrote in message
...
=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100="YES")),C1:C100)

Also, in this application the double unary is redundant. Adding the 2
arrays will coerce the Boolean to numeric:

=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100="YES")),C1:C 100)

As mine does (licks finger, paints the sky!)

--
__________________________________
HTH

Bob

"Glenn" wrote in message
...
Bernd P wrote:
Bob Philips has a great page on SUMPRODUCT and double unaries '--'
He also has 2 ls in his surname, Welsh origins not Dutch <bg
Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault" ))"]


Both ranges are the same and since the same cell can't equal both "Ford"
and "Renault", this will produce the expected result.



Exactly!


to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]


=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100=”YES”)),C1:C100)

Two distinct ranges. Since A1=-3 and B1="Yes" is possible, it is a
different problem requiring a more complex solution.


and judge yourself.



Both solutions appear to be valid, but one is more robust.


No more robust, my example is specifically showing an OR on the same range,
in such circumstances it will not fail so it is robust. You have to look at
it in the context of how/where it is presented.

"T. Valko" wrote:
=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100="YES")),C1:C100)

Also, in this application the double unary is redundant.
[....]
=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100="YES")),C1:C 100)

Or:

=SUMPRODUCT((A1:A100<0)+(B1:B100="YES")0, C1:C100)

Since I suspect that Excel treats SIGN as an operator, not a bona fide
function with its concomitant overhead, it is unclear whether my suggestion
is more efficient. But it requires 4 fewer keystrokes to type :-) :-).


----- original message -----

"T. Valko" wrote in message
...
=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100="YES")),C1:C100)

Also, in this application the double unary is redundant. Adding the 2
arrays will coerce the Boolean to numeric:

=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100="YES")),C1:C 100)

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Bernd P wrote:
Bob Philips has a great page on SUMPRODUCT and double unaries '--'
He also has 2 ls in his surname, Welsh origins not Dutch <bg
Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault" ))"]


Both ranges are the same and since the same cell can't equal both "Ford"
and "Renault", this will produce the expected result.


to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]


=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100="YES")),C1:C100)

Two distinct ranges. Since A1=-3 and B1="Yes" is possible, it is a
different problem requiring a more complex solution.


and judge yourself.



Both solutions appear to be valid, but one is more robust.


Regards,
Bernd

Bob Phillips wrote:

"No more robust, my example is specifically showing an OR on the same range,
in such circumstances it will not fail so it is robust. You have to look at
it in the context of how/where it is presented."


I guess what I meant was that Bernd's solution would work for either problem and
yours wouldn't.

Also, in this application the double unary is redundant. Adding the 2 arrays
will coerce the Boolean to numeric:
Hello Biff,

Thank you - adjusted.

Regards,
Bernd

Since I suspect that Excel treats SIGN as an operator, not a bona fide
function with its concomitant overhead, it is unclear whether my suggestion
is more efficient. *But it requires 4 fewer keystrokes to type :-) :-).

Hello,

That is possible and also far better than just to add the OR criteria
checks.
But I prefer the SIGN() function - it is clearly showing that we deal
with OR criteria here. This is similar to the double unary minus
operator which clearly indicates a criterion check.

But let us keep in mind that we talk about Excel's biggest time
waster: SUMPRODUCT :-)

Regards,
Bernd

"Bernd P" wrote:
But I prefer the SIGN() function

To each his own.

But philosophically, I think "0" is the better choice. It clearly
indicates that we expect only values greater than zero to be valid.

When I saw SIGN, my first reaction was: "Why is the guy using SIGN? The
particular argument can never evaluate to -1. So what does he expect?"

Then I thought: "Okay, it works. But why use a function when an operator
will do just fine?" Then I remembered that IF() is treated like an
operator, not a function, so I thought perhaps that is true for all internal
functions. So there probably is no performance issue. Nonetheless, the use
of SIGN __looks__ inefficient there. And of course, it counts toward the
Excel 2003 nesting limit of 7.

But hey, y'can lead a horse to water, but y'can't make him drink.


----- original message -----

"Bernd P" wrote in message
...

Since I suspect that Excel treats SIGN as an operator, not a bona fide
function with its concomitant overhead, it is unclear whether my
suggestion
is more efficient. But it requires 4 fewer keystrokes to type :-) :-).

Hello,

That is possible and also far better than just to add the OR criteria
checks.
But I prefer the SIGN() function - it is clearly showing that we deal
with OR criteria here. This is similar to the double unary minus
operator which clearly indicates a criterion check.

But let us keep in mind that we talk about Excel's biggest time
waster: SUMPRODUCT :-)

Regards,
Bernd

But let us keep in mind that we talk about Excel's
biggest time waster: SUMPRODUCT

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel ©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...

Since I suspect that Excel treats SIGN as an operator, not a bona fide
function with its concomitant overhead, it is unclear whether my
suggestion
is more efficient. But it requires 4 fewer keystrokes to type :-) :-).

Hello,

That is possible and also far better than just to add the OR criteria
checks.
But I prefer the SIGN() function - it is clearly showing that we deal
with OR criteria here. This is similar to the double unary minus
operator which clearly indicates a criterion check.

But let us keep in mind that we talk about Excel's biggest time
waster: SUMPRODUCT :-)

Regards,
Bernd

To each his own.

But philosophically, I think "0" is the better choice. *It clearly
indicates that we expect only values greater than zero to be valid.

When I saw SIGN, my first reaction was: *"Why is the guy using SIGN? *The
particular argument can never evaluate to -1. *So what does he expect?"

Then I thought: *"Okay, it works. *But why use a function when an operator
will do just fine?" *Then I remembered that IF() is treated like an
operator, not a function, so I thought perhaps that is true for all internal
functions. *So there probably is no performance issue. *Nonetheless, the use
of SIGN __looks__ inefficient there. *And of course, it counts toward the
Excel 2003 nesting limit of 7.

But hey, y'can lead a horse to water, but y'can't make him drink.

Hello,

Your ()0 is only doing half of the job because its not resulting in
numbers, just boolean values. In order to use it generally within
nested or combined conditions (example: an AND of two OR conditions)
you will need to wrap it into paranthesis and to double unary it (or
similar). So: welcome back to the same number of keystrokes and
nesting levels.

Regards,
Bernd

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel ©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g

Hello Biff,

No.

1. For some problems you should use SUMPRODUCT, for some you should
use my UDF's (or other special-purpose UDF's) and to some others you
should apply pivot tables.

2. SUMPRODUCT is offered here (and then used or applied) far too
often.

These two thesises are not forming a contradiction.

Regards,
Bernd

2. SUMPRODUCT is offered here (and then used or applied) far too often.

Maybe the reason it's applied so often is because it's a very powerful and
useful counting/summing/"lookup" function.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel
©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g

Hello Biff,

No.

1. For some problems you should use SUMPRODUCT, for some you should
use my UDF's (or other special-purpose UDF's) and to some others you
should apply pivot tables.

2. SUMPRODUCT is offered here (and then used or applied) far too
often.

These two thesises are not forming a contradiction.

Regards,
Bernd

Maybe the reason it's applied so often is because it's a very powerful and
useful counting/summing/"lookup" function.

Sorry, no again. That's the reason why it got overstated <g

Regards,
Bernd

Sorry, no again.

Of course you're entitled to your own opinion.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...

Maybe the reason it's applied so often is because it's a very powerful
and
useful counting/summing/"lookup" function.

Sorry, no again. That's the reason why it got overstated <g

Regards,
Bernd

"Bernd P" wrote:
Your ()0 is only doing half of the job
[....]
you will need to wrap it into paranthesis and to
double unary it (or similar). So: welcome back to
the same number of keystrokes

Yeah, that was an important criterion ;-) ;-).


and nesting levels.

I was referring to the Excel 2003 function nesting level.


----- original message -----

"Bernd P" wrote in message
...
To each his own.

But philosophically, I think "0" is the better choice. It clearly
indicates that we expect only values greater than zero to be valid.

When I saw SIGN, my first reaction was: "Why is the guy using SIGN? The
particular argument can never evaluate to -1. So what does he expect?"

Then I thought: "Okay, it works. But why use a function when an operator
will do just fine?" Then I remembered that IF() is treated like an
operator, not a function, so I thought perhaps that is true for all
internal
functions. So there probably is no performance issue. Nonetheless, the use
of SIGN __looks__ inefficient there. And of course, it counts toward the
Excel 2003 nesting limit of 7.

But hey, y'can lead a horse to water, but y'can't make him drink.

Hello,

Your ()0 is only doing half of the job because its not resulting in
numbers, just boolean values. In order to use it generally within
nested or combined conditions (example: an AND of two OR conditions)
you will need to wrap it into paranthesis and to double unary it (or
similar). So: welcome back to the same number of keystrokes and
nesting levels.

Regards,
Bernd

I think you are being far too purist, far too dogmatic, when you probably
have no idea of what the OP knows/wants/has to achieve, just what he has
posted in their plea for help.

In most questions asked here and on the forums, the OP needs a solution, SP
often provides that spectacularly well. Of course, if it is used, extended
over many cells, the performance will suffer. In those circumstances, the OP
would (should) come back and seek further help. I think the fact that we
seen very few such requests attests to the validity of the solutions
offered.

SP can be abused, as can UDFs. You are whistling in the wind Bernd.

Bob

"Bernd P" wrote in message
...

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel
©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g

Hello Biff,

No.

1. For some problems you should use SUMPRODUCT, for some you should
use my UDF's (or other special-purpose UDF's) and to some others you
should apply pivot tables.

2. SUMPRODUCT is offered here (and then used or applied) far too
often.

These two thesises are not forming a contradiction.

Regards,
Be

On 2 Okt., 12:07, "Bob Phillips" wrote:
I think you are being far too purist, far too dogmatic, when you probably
have no idea of what the OP knows/wants/has to achieve, just what he has
posted in their plea for help.

In most questions asked here and on the forums, the OP needs a solution, SP
often provides that spectacularly well. Of course, if it is used, extended
over many cells, the performance will suffer. In those circumstances, the OP
would (should) come back and seek further help. I think the fact that we
seen very few such requests attests to the validity of the solutions
offered.

SP can be abused, as can UDFs. You are whistling in the wind Bernd.

Bob

Hello Bob,

OT:

You are entitled to your own opinion. Which is different from mine, as
it seems.

My thesis about SUMPRODUCT might be proven or rejected with
statistical evidence over time.

Fact is that your current SUMPRODUCT example for an OR is showing XOR
criteria and it would fail for overlapping OR ones (for which you do
not offer a solution).

Regards,
Bernd

"Bernd P" wrote:
Fact is that your current SUMPRODUCT example for an
OR is showing XOR criteria and it would fail for
overlapping OR ones (for which you do not offer a solution).

I presume you are talking about the example:

=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))

The correct statement is: it works when the conditions are
__mutually-exclusive__. You are correct that it does not work otherwise.

XOR is an operation, not a criterion.

I am not nitpicking your use misuse of terms. The formula above does
__not__ implement an XOR insofar as it does not return FALSE when both
conditions are met.

(That is what confused me when I read your web page. Now I understand what
you were trying to say.)

Of course, both conditions cannot be met in Bob's example; ergo, it is
__not__ incorrect for the specific case.

But if one of the ranges were B1:B10 instead of A1:A10, then mutual
exclusion is an important issue.

It would be prudent for Bob to state that in his example, or provide a
second example that demonstrates how non-mutually exclusive conditions
should be handled. As I stated, I think the preferred method would be:

=SUMPRODUCT(((A1:A10="Ford")+(B1:B10="Renault")0) )

although "<0" is a reasonable alternative.

The unneeded use of another function, like SIGN as you do, is deprecated
because it will not work in some cases in Excel 2003, namely when the user
reaches the function nesting level of 7. Then, the hapless user would need
to find an alternative form, namely the above. So you might as well do it
the way that works more generally.

(Of course, it would probably be better to pick a different example
altogether. I'm just piggybacking Bob's example to demonstrate form.)


----- original message -----

"Bernd P" wrote in message
...
On 2 Okt., 12:07, "Bob Phillips" wrote:
I think you are being far too purist, far too dogmatic, when you probably
have no idea of what the OP knows/wants/has to achieve, just what he has
posted in their plea for help.

In most questions asked here and on the forums, the OP needs a solution,
SP
often provides that spectacularly well. Of course, if it is used,
extended
over many cells, the performance will suffer. In those circumstances, the
OP
would (should) come back and seek further help. I think the fact that we
seen very few such requests attests to the validity of the solutions
offered.

SP can be abused, as can UDFs. You are whistling in the wind Bernd.

Bob

Hello Bob,

OT:

You are entitled to your own opinion. Which is different from mine, as
it seems.

My thesis about SUMPRODUCT might be proven or rejected with
statistical evidence over time.

Fact is that your current SUMPRODUCT example for an OR is showing XOR
criteria and it would fail for overlapping OR ones (for which you do
not offer a solution).

Regards,
Bernd

Nitpick errata....

I wrote:
The formula above does __not__ implement an XOR insofar
as it does not return FALSE when both conditions are met.

I should say the "__equivalent__ of FALSE", namely zero, since the
expression returns numeric values, not boolean (true/false) values.


----- original message -----

"JoeU2004" wrote in message
...
"Bernd P" wrote:
Fact is that your current SUMPRODUCT example for an
OR is showing XOR criteria and it would fail for
overlapping OR ones (for which you do not offer a solution).

I presume you are talking about the example:

=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))

The correct statement is: it works when the conditions are
__mutually-exclusive__. You are correct that it does not work otherwise.

XOR is an operation, not a criterion.

I am not nitpicking your use misuse of terms. The formula above does
__not__ implement an XOR insofar as it does not return FALSE when both
conditions are met.

(That is what confused me when I read your web page. Now I understand
what you were trying to say.)

Of course, both conditions cannot be met in Bob's example; ergo, it is
__not__ incorrect for the specific case.

But if one of the ranges were B1:B10 instead of A1:A10, then mutual
exclusion is an important issue.

It would be prudent for Bob to state that in his example, or provide a
second example that demonstrates how non-mutually exclusive conditions
should be handled. As I stated, I think the preferred method would be:

=SUMPRODUCT(((A1:A10="Ford")+(B1:B10="Renault")0) )

although "<0" is a reasonable alternative.

The unneeded use of another function, like SIGN as you do, is deprecated
because it will not work in some cases in Excel 2003, namely when the user
reaches the function nesting level of 7. Then, the hapless user would
need to find an alternative form, namely the above. So you might as well
do it the way that works more generally.

(Of course, it would probably be better to pick a different example
altogether. I'm just piggybacking Bob's example to demonstrate form.)


----- original message -----

"Bernd P" wrote in message
...
On 2 Okt., 12:07, "Bob Phillips" wrote:
I think you are being far too purist, far too dogmatic, when you
probably
have no idea of what the OP knows/wants/has to achieve, just what he has
posted in their plea for help.

In most questions asked here and on the forums, the OP needs a solution,
SP
often provides that spectacularly well. Of course, if it is used,
extended
over many cells, the performance will suffer. In those circumstances,
the OP
would (should) come back and seek further help. I think the fact that we
seen very few such requests attests to the validity of the solutions
offered.

SP can be abused, as can UDFs. You are whistling in the wind Bernd.

Bob

Hello Bob,

OT:

You are entitled to your own opinion. Which is different from mine, as
it seems.

My thesis about SUMPRODUCT might be proven or rejected with
statistical evidence over time.

Fact is that your current SUMPRODUCT example for an OR is showing XOR
criteria and it would fail for overlapping OR ones (for which you do
not offer a solution).

Regards,
Bernd

Errata....

I wrote:
=SUMPRODUCT(((A1:A10="Ford")+(B1:B10="Renault")0) )

Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))



----- original message -----

"JoeU2004" wrote in message
...
"Bernd P" wrote:
Fact is that your current SUMPRODUCT example for an
OR is showing XOR criteria and it would fail for
overlapping OR ones (for which you do not offer a solution).

I presume you are talking about the example:

=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))

The correct statement is: it works when the conditions are
__mutually-exclusive__. You are correct that it does not work otherwise.

XOR is an operation, not a criterion.

I am not nitpicking your use misuse of terms. The formula above does
__not__ implement an XOR insofar as it does not return FALSE when both
conditions are met.

(That is what confused me when I read your web page. Now I understand
what you were trying to say.)

Of course, both conditions cannot be met in Bob's example; ergo, it is
__not__ incorrect for the specific case.

But if one of the ranges were B1:B10 instead of A1:A10, then mutual
exclusion is an important issue.

It would be prudent for Bob to state that in his example, or provide a
second example that demonstrates how non-mutually exclusive conditions
should be handled. As I stated, I think the preferred method would be:

=SUMPRODUCT(((A1:A10="Ford")+(B1:B10="Renault")0) )

although "<0" is a reasonable alternative.

The unneeded use of another function, like SIGN as you do, is deprecated
because it will not work in some cases in Excel 2003, namely when the user
reaches the function nesting level of 7. Then, the hapless user would
need to find an alternative form, namely the above. So you might as well
do it the way that works more generally.

(Of course, it would probably be better to pick a different example
altogether. I'm just piggybacking Bob's example to demonstrate form.)


----- original message -----

"Bernd P" wrote in message
...
On 2 Okt., 12:07, "Bob Phillips" wrote:
I think you are being far too purist, far too dogmatic, when you
probably
have no idea of what the OP knows/wants/has to achieve, just what he has
posted in their plea for help.

In most questions asked here and on the forums, the OP needs a solution,
SP
often provides that spectacularly well. Of course, if it is used,
extended
over many cells, the performance will suffer. In those circumstances,
the OP
would (should) come back and seek further help. I think the fact that we
seen very few such requests attests to the validity of the solutions
offered.

SP can be abused, as can UDFs. You are whistling in the wind Bernd.

Bob

Hello Bob,

OT:

You are entitled to your own opinion. Which is different from mine, as
it seems.

My thesis about SUMPRODUCT might be proven or rejected with
statistical evidence over time.

Fact is that your current SUMPRODUCT example for an OR is showing XOR
criteria and it would fail for overlapping OR ones (for which you do
not offer a solution).

Regards,
Bernd

Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd

"Bernd P" wrote:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))
[....]
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

[...] same nesting level

No it isn't. I said "function nesting level of 7". (I meant to write
"limit" instead of "level". But either one works in this context.)

Excel 2003 has a limit of 7 nested functions (8, counting the outermost). I
don't know if Excel has limit on expression nesting (other than the formula
character limit), but if it does, it's certainly more than 7. (I tried only
12, with no error.)


SIGN serves as a nice and decent reminder that
we apply OR criteria

The plus sign does that, not SIGN. I found SIGN confusing in this context;
that is, until I understood what you were trying to accomplish with it.
IMHO, "0" communicates that more straight-forwardly.


in short: simply better.

That is a matter of opinion. Reasonable people can disagree.

I already knew your unwavering opinion from elsewhere in this thread. I was
writing my opinion for Bob and others, not that Bob really needs to hear it.


On 10,000 rows your version needs 7ms, mine 6ms on
my dual core proc. Not that it matters here, but...

I agree that the small total difference would not make any difference to me.

But I'm impressed by your numbers.

I get a difference of about 165.132 msec for "0" and 153.731 msec for
"SIGN". "SIGN" is about 7% faster. (But again, 11.4 msec per 10,000 is not
enough for me adopt a different style.)

I have a single-core 2.13GHz CPU running at 2.08GHz. "My Computer"
Properties now shows a memory speed of 2.13GHz. I coulda sworn it showed
about 768MHz (or 786?) earlier. The latter is what I remember vaguely from
the specs when I bought my laptop 4 years ago.


----- original message -----

"Bernd P" wrote in message
...
Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd

I had moved on to the point (that you raised) that ... SUMPRODUCT is offered
here (and then used or applied) far too often... and you drag it back to a
topic that I thought we had agreed to disagree on days ago. Sigh!


Bob

"Bernd P" wrote in message
...
On 2 Okt., 12:07, "Bob Phillips" wrote:
I think you are being far too purist, far too dogmatic, when you probably
have no idea of what the OP knows/wants/has to achieve, just what he has
posted in their plea for help.

In most questions asked here and on the forums, the OP needs a solution,
SP
often provides that spectacularly well. Of course, if it is used,
extended
over many cells, the performance will suffer. In those circumstances, the
OP
would (should) come back and seek further help. I think the fact that we
seen very few such requests attests to the validity of the solutions
offered.

SP can be abused, as can UDFs. You are whistling in the wind Bernd.

Bob

Hello Bob,

OT:

You are entitled to your own opinion. Which is different from mine, as
it seems.

My thesis about SUMPRODUCT might be proven or rejected with
statistical evidence over time.

Fact is that your current SUMPRODUCT example for an OR is showing XOR
criteria and it would fail for overlapping OR ones (for which you do
not offer a solution).

Regards,
Bernd

Just to throw some more gasoline on this fire....

--((A1:A10="x")+(A1:A10="y")0)

Is far more intuitive than:

SIGN((A1:A10="x")+(A1:A10="y"))

Now, to spoil everyone's argument...

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a single
one dimensional array in a formula with multiple conditions.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"Bernd P" wrote:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))
[....]
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

[...] same nesting level

No it isn't. I said "function nesting level of 7". (I meant to write
"limit" instead of "level". But either one works in this context.)

Excel 2003 has a limit of 7 nested functions (8, counting the outermost).
I don't know if Excel has limit on expression nesting (other than the
formula character limit), but if it does, it's certainly more than 7. (I
tried only 12, with no error.)


SIGN serves as a nice and decent reminder that
we apply OR criteria

The plus sign does that, not SIGN. I found SIGN confusing in this
context; that is, until I understood what you were trying to accomplish
with it. IMHO, "0" communicates that more straight-forwardly.


in short: simply better.

That is a matter of opinion. Reasonable people can disagree.

I already knew your unwavering opinion from elsewhere in this thread. I
was writing my opinion for Bob and others, not that Bob really needs to
hear it.


On 10,000 rows your version needs 7ms, mine 6ms on
my dual core proc. Not that it matters here, but...

I agree that the small total difference would not make any difference to
me.

But I'm impressed by your numbers.

I get a difference of about 165.132 msec for "0" and 153.731 msec for
"SIGN". "SIGN" is about 7% faster. (But again, 11.4 msec per 10,000 is
not enough for me adopt a different style.)

I have a single-core 2.13GHz CPU running at 2.08GHz. "My Computer"
Properties now shows a memory speed of 2.13GHz. I coulda sworn it showed
about 768MHz (or 786?) earlier. The latter is what I remember vaguely
from the specs when I bought my laptop 4 years ago.


----- original message -----

"Bernd P" wrote in message
...
Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd

--((A1:A10="x")+(A1:A10="y")0)
SIGN((A1:A10="x")+(A1:A10="y"))

Eh, those might be bad examples but I'm sure you all get my drift.

--((A1:A10="x")+(B1:B10="y")0)
SIGN((A1:A10="x")+(B1:B10="y"))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Just to throw some more gasoline on this fire....

--((A1:A10="x")+(A1:A10="y")0)

Is far more intuitive than:

SIGN((A1:A10="x")+(A1:A10="y"))

Now, to spoil everyone's argument...

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a single
one dimensional array in a formula with multiple conditions.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"Bernd P" wrote:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))
[....]
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

[...] same nesting level

No it isn't. I said "function nesting level of 7". (I meant to write
"limit" instead of "level". But either one works in this context.)

Excel 2003 has a limit of 7 nested functions (8, counting the outermost).
I don't know if Excel has limit on expression nesting (other than the
formula character limit), but if it does, it's certainly more than 7. (I
tried only 12, with no error.)


SIGN serves as a nice and decent reminder that
we apply OR criteria

The plus sign does that, not SIGN. I found SIGN confusing in this
context; that is, until I understood what you were trying to accomplish
with it. IMHO, "0" communicates that more straight-forwardly.


in short: simply better.

That is a matter of opinion. Reasonable people can disagree.

I already knew your unwavering opinion from elsewhere in this thread. I
was writing my opinion for Bob and others, not that Bob really needs to
hear it.


On 10,000 rows your version needs 7ms, mine 6ms on
my dual core proc. Not that it matters here, but...

I agree that the small total difference would not make any difference to
me.

But I'm impressed by your numbers.

I get a difference of about 165.132 msec for "0" and 153.731 msec for
"SIGN". "SIGN" is about 7% faster. (But again, 11.4 msec per 10,000 is
not enough for me adopt a different style.)

I have a single-core 2.13GHz CPU running at 2.08GHz. "My Computer"
Properties now shows a memory speed of 2.13GHz. I coulda sworn it showed
about 768MHz (or 786?) earlier. The latter is what I remember vaguely
from the specs when I bought my laptop 4 years ago.


----- original message -----

"Bernd P" wrote in message
...
Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd

"T. Valko" wrote:
Just to throw some more gasoline on this fire....
--((A1:A10="x")+(A1:A10="y")0)
Is far more intuitive

Thanks. I believe I said that several times in this thread. We're just
talking to ourselves ;-).


----- original message -----

"T. Valko" wrote in message
...
Just to throw some more gasoline on this fire....

--((A1:A10="x")+(A1:A10="y")0)

Is far more intuitive than:

SIGN((A1:A10="x")+(A1:A10="y"))

Now, to spoil everyone's argument...

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a single
one dimensional array in a formula with multiple conditions.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"Bernd P" wrote:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))
[....]
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

[...] same nesting level

No it isn't. I said "function nesting level of 7". (I meant to write
"limit" instead of "level". But either one works in this context.)

Excel 2003 has a limit of 7 nested functions (8, counting the outermost).
I don't know if Excel has limit on expression nesting (other than the
formula character limit), but if it does, it's certainly more than 7. (I
tried only 12, with no error.)


SIGN serves as a nice and decent reminder that
we apply OR criteria

The plus sign does that, not SIGN. I found SIGN confusing in this
context; that is, until I understood what you were trying to accomplish
with it. IMHO, "0" communicates that more straight-forwardly.


in short: simply better.

That is a matter of opinion. Reasonable people can disagree.

I already knew your unwavering opinion from elsewhere in this thread. I
was writing my opinion for Bob and others, not that Bob really needs to
hear it.


On 10,000 rows your version needs 7ms, mine 6ms on
my dual core proc. Not that it matters here, but...

I agree that the small total difference would not make any difference to
me.

But I'm impressed by your numbers.

I get a difference of about 165.132 msec for "0" and 153.731 msec for
"SIGN". "SIGN" is about 7% faster. (But again, 11.4 msec per 10,000 is
not enough for me adopt a different style.)

I have a single-core 2.13GHz CPU running at 2.08GHz. "My Computer"
Properties now shows a memory speed of 2.13GHz. I coulda sworn it showed
about 768MHz (or 786?) earlier. The latter is what I remember vaguely
from the specs when I bought my laptop 4 years ago.


----- original message -----

"Bernd P" wrote in message
...
Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd

I believe I said that several times in this thread.

Yes, I'm backing your position! Although, I have on occasion used SIGN. It
just depends on what I think of at the moment.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
Just to throw some more gasoline on this fire....
--((A1:A10="x")+(A1:A10="y")0)
Is far more intuitive

Thanks. I believe I said that several times in this thread. We're just
talking to ourselves ;-).


----- original message -----

"T. Valko" wrote in message
...
Just to throw some more gasoline on this fire....

--((A1:A10="x")+(A1:A10="y")0)

Is far more intuitive than:

SIGN((A1:A10="x")+(A1:A10="y"))

Now, to spoil everyone's argument...

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a single
one dimensional array in a formula with multiple conditions.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"Bernd P" wrote:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))
[....]
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

[...] same nesting level

No it isn't. I said "function nesting level of 7". (I meant to write
"limit" instead of "level". But either one works in this context.)

Excel 2003 has a limit of 7 nested functions (8, counting the
outermost). I don't know if Excel has limit on expression nesting (other
than the formula character limit), but if it does, it's certainly more
than 7. (I tried only 12, with no error.)


SIGN serves as a nice and decent reminder that
we apply OR criteria

The plus sign does that, not SIGN. I found SIGN confusing in this
context; that is, until I understood what you were trying to accomplish
with it. IMHO, "0" communicates that more straight-forwardly.


in short: simply better.

That is a matter of opinion. Reasonable people can disagree.

I already knew your unwavering opinion from elsewhere in this thread. I
was writing my opinion for Bob and others, not that Bob really needs to
hear it.


On 10,000 rows your version needs 7ms, mine 6ms on
my dual core proc. Not that it matters here, but...

I agree that the small total difference would not make any difference to
me.

But I'm impressed by your numbers.

I get a difference of about 165.132 msec for "0" and 153.731 msec for
"SIGN". "SIGN" is about 7% faster. (But again, 11.4 msec per 10,000 is
not enough for me adopt a different style.)

I have a single-core 2.13GHz CPU running at 2.08GHz. "My Computer"
Properties now shows a memory speed of 2.13GHz. I coulda sworn it
showed about 768MHz (or 786?) earlier. The latter is what I remember
vaguely from the specs when I bought my laptop 4 years ago.


----- original message -----

"Bernd P" wrote in message
...
Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a single
one dimensional array in a formula with multiple conditions.

Hello Biff,

OT:
You mean --ISNUMBER(MATCH())? Very interesting.

But it takes quite a long array to make it quicker than SIGN() on
average, I guess.

Regards,
Bernd

I agree with you Biff, ISNUMBER makes far more sense, SIGN makes the least
(although I too have been offering SIGN for many years. I don't recall
anyone using it before Frank Kabel and I came up with it in a NETWORKDAYS
solution - not saying we were first, but we certainly arrived at the
solution independently).

But this thread is really dead now, isn't it best left to wither?

Bob

"T. Valko" wrote in message
...
I believe I said that several times in this thread.

Yes, I'm backing your position! Although, I have on occasion used SIGN. It
just depends on what I think of at the moment.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"T. Valko" wrote:
Just to throw some more gasoline on this fire....
--((A1:A10="x")+(A1:A10="y")0)
Is far more intuitive

Thanks. I believe I said that several times in this thread. We're just
talking to ourselves ;-).


----- original message -----

"T. Valko" wrote in message
...
Just to throw some more gasoline on this fire....

--((A1:A10="x")+(A1:A10="y")0)

Is far more intuitive than:

SIGN((A1:A10="x")+(A1:A10="y"))

Now, to spoil everyone's argument...

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a
single one dimensional array in a formula with multiple conditions.

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"Bernd P" wrote:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))
[....]
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

[...] same nesting level

No it isn't. I said "function nesting level of 7". (I meant to write
"limit" instead of "level". But either one works in this context.)

Excel 2003 has a limit of 7 nested functions (8, counting the
outermost). I don't know if Excel has limit on expression nesting
(other than the formula character limit), but if it does, it's
certainly more than 7. (I tried only 12, with no error.)


SIGN serves as a nice and decent reminder that
we apply OR criteria

The plus sign does that, not SIGN. I found SIGN confusing in this
context; that is, until I understood what you were trying to accomplish
with it. IMHO, "0" communicates that more straight-forwardly.


in short: simply better.

That is a matter of opinion. Reasonable people can disagree.

I already knew your unwavering opinion from elsewhere in this thread.
I was writing my opinion for Bob and others, not that Bob really needs
to hear it.


On 10,000 rows your version needs 7ms, mine 6ms on
my dual core proc. Not that it matters here, but...

I agree that the small total difference would not make any difference
to me.

But I'm impressed by your numbers.

I get a difference of about 165.132 msec for "0" and 153.731 msec for
"SIGN". "SIGN" is about 7% faster. (But again, 11.4 msec per 10,000
is not enough for me adopt a different style.)

I have a single-core 2.13GHz CPU running at 2.08GHz. "My Computer"
Properties now shows a memory speed of 2.13GHz. I coulda sworn it
showed about 768MHz (or 786?) earlier. The latter is what I remember
vaguely from the specs when I bought my laptop 4 years ago.


----- original message -----

"Bernd P" wrote in message
...
Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd

On Oct 3, 8:28*am, "JoeU2004" wrote:
"T. Valko" wrote:
Just to throw some more gasoline on this fire....
--((A1:A10="x")+(A1:A10="y")0)
Is far more intuitive

Thanks. *I believe I said that several times in this thread. *We're just
talking to ourselves ;-).

----- original message -----

"T. Valko" wrote in message

... Just to throw some more gasoline on this fire....

--((A1:A10="x")+(A1:A10="y")0)

Is far more intuitive than:

SIGN((A1:A10="x")+(A1:A10="y"))

Now, to spoil everyone's argument...

ISNUMBER(MATCH is probably the best way to go when doing "OR" on a single
one dimensional array in a formula with multiple conditions.

--
Biff
Microsoft Excel MVP

"JoeU2004" wrote in message
...
"Bernd P" wrote:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))
[....]
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

[...] same nesting level

No it isn't. *I said "function nesting level of 7". *(I meant to write
"limit" instead of "level". *But either one works in this context.)

Excel 2003 has a limit of 7 nested functions (8, counting the outermost).
I don't know if Excel has limit on expression nesting (other than the
formula character limit), but if it does, it's certainly more than 7. *(I
tried only 12, with no error.)

SIGN serves as a nice and decent reminder that
we apply OR criteria

The plus sign does that, not SIGN. *I found SIGN confusing in this
context; that is, until I understood what you were trying to accomplish
with it. IMHO, "0" communicates that more straight-forwardly.

in short: simply better.

That is a matter of opinion. *Reasonable people can disagree.

I already knew your unwavering opinion from elsewhere in this thread. *I
was writing my opinion for Bob and others, not that Bob really needs to
hear it.

On 10,000 rows your version needs 7ms, mine 6ms on
my dual core proc. *Not that it matters here, but...

I agree that the small total difference would not make any difference to
me.

But I'm impressed by your numbers.

I get a difference of about 165.132 msec for "0" and 153.731 msec for
"SIGN". *"SIGN" is about 7% faster. *(But again, 11.4 msec per 10,000 is
not enough for me adopt a different style.)

I have a single-core 2.13GHz CPU running at 2.08GHz. *"My Computer"
Properties now shows a memory speed of 2.13GHz. *I coulda sworn it showed
about 768MHz (or 786?) earlier. *The latter is what I remember vaguely
from the specs when I bought my laptop 4 years ago.

----- original message -----

"Bernd P" wrote in message
....
Of course, that should be:

=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="Renault")0))

Hello,

OT:
Now compare that to:
=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="Renault" )))

Less function points, same nesting level, and SIGN serves as a nice
and decent reminder that we apply OR criteria; in short: simply
better.

On 10,000 rows your version needs 7ms, mine 6ms on my dual core proc.
Not that it matters here, but...

Regards,
Bernd
told me method how to used mid formula in excel sheet