Sumproduct Fucntion - the use of dash ( -- )
 

In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?

Sumproduct Fucntion - the use of dash ( -- )
 

The terms in brackets in a sumproduct formula will be logical terms
and thus will return values of FALSE or TRUE. Using a single minus
will change these to 0 and -1, and a double minus converts them to 0
or 1, so they can be used in arithmetic. You can use them like this in
a SP formula:

=SUMPRODUCT(--(condition1),--(condition2),--(condition3))

but an alternative to this is:

=SUMPRODUCT((condition1)*(condition2)*(condition3) )

Hope this helps.

Pete

On Apr 4, 1:21*pm, Negda wrote:
In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?

Sumproduct Fucntion - the use of dash ( -- )
 

In this formula: =SUMPRODUCT(--(A1:A10="Open"))

this section: (A1:A10="Open")
returns a series of TRUE/FALSE values
....which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to
a TRUE/FALSE value, Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.

It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

You could also use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, in the formula, the TRUE/FALSE values
are converted to 1's and 0's by the "--" and
SUMPRODUCT calculates the result.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Negda" wrote in message
...
In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?

Sumproduct Fucntion - the use of dash ( -- )
 

On Apr 4, 3:29*pm, Pete_UK wrote:
The terms in brackets in a sumproduct formula will be logical terms
and thus will return values of FALSE or TRUE. Using a single minus
will change these to 0 and -1, and a double minus converts them to 0
or 1, so they can be used in arithmetic. You can use them like this in
a SP formula:

=SUMPRODUCT(--(condition1),--(condition2),--(condition3))

but an alternative to this is:

=SUMPRODUCT((condition1)*(condition2)*(condition3) )

Hope this helps.

Pete

On Apr 4, 1:21*pm, Negda wrote:



In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?- Hide quoted text -

- Show quoted text -

thank you very much - helps a lot
Negda

Sumproduct Fucntion - the use of dash ( -- )
 

On Apr 4, 3:37*pm, "Ron Coderre"
wrote:
In this formula: =SUMPRODUCT(--(A1:A10="Open"))

this section: (A1:A10="Open")
returns a series of TRUE/FALSE values
...which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to
a TRUE/FALSE value, Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.

It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

You could also use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, in the formula, the TRUE/FALSE values
are converted to 1's and 0's by the "--" and
SUMPRODUCT calculates the result.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Negda" wrote in message

...



In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?- Hide quoted text -

- Show quoted text -

Ron,
Yes, it helps - thanks
Negda

Sumproduct Fucntion - the use of dash ( -- )
 

Look here for a robust explanation of the Unary Operator:
http://bhinneka.blogspot.com/2008_02_01_archive.html

Regards,
Ryan---

--
RyGuy


"Negda" wrote:

On Apr 4, 3:37 pm, "Ron Coderre"
wrote:
In this formula: =SUMPRODUCT(--(A1:A10="Open"))

this section: (A1:A10="Open")
returns a series of TRUE/FALSE values
...which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to
a TRUE/FALSE value, Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.

It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

You could also use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, in the formula, the TRUE/FALSE values
are converted to 1's and 0's by the "--" and
SUMPRODUCT calculates the result.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Negda" wrote in message

...



In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?- Hide quoted text -

- Show quoted text -

Ron,
Yes, it helps - thanks
Negda

Sumproduct Fucntion - the use of dash ( -- )
 

You're welcome - thanks for taking the trouble to feed back.

Pete

On Apr 4, 1:55*pm, Negda wrote:
On Apr 4, 3:29*pm, Pete_UK wrote:





The terms in brackets in a sumproduct formula will be logical terms
and thus will return values of FALSE or TRUE. Using a single minus
will change these to 0 and -1, and a double minus converts them to 0
or 1, so they can be used in arithmetic. You can use them like this in
a SP formula:

=SUMPRODUCT(--(condition1),--(condition2),--(condition3))

but an alternative to this is:

=SUMPRODUCT((condition1)*(condition2)*(condition3) )

Hope this helps.

Pete

On Apr 4, 1:21*pm, Negda wrote:

In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?- Hide quoted text -

- Show quoted text -

thank you very much - helps a lot
Negda- Hide quoted text -

- Show quoted text -

Sumproduct Fucntion - the use of dash ( -- )
 

Ron Coderre wrote:
In this formula: =SUMPRODUCT(--(A1:A10="Open"))

this section: (A1:A10="Open")
returns a series of TRUE/FALSE values
...which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to
a TRUE/FALSE value, Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.

It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

You could also use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, in the formula, the TRUE/FALSE values
are converted to 1's and 0's by the "--" and
SUMPRODUCT calculates the result.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Negda" wrote in message
...
In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?



I've been trying to sum multiple columns where a condition does not
exeist in one of the columns. Example: columns x, y & z where z doesn't
contain "this condition".

I can understand that these work:
=SUMPRODUCT((condition1)*(condition2)*(condition3) )
and even =SUMPRODUCT(--(condition1),--(condition2),--(condition3))
but I can't understand how to get to my desired results. Would I write
the formula like this?
=SUMPRODUCT((condition1)*(condition2)-(condition3))
=SUMPRODUCT(--(condition1),--(condition2),-(condition3))

TIA

Sumproduct Fucntion - the use of dash ( -- )
 

Can you give some more details about the
kind of test you want to perform on Col_Z?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"plb2862" wrote in message
...
I've been trying to sum multiple columns where a condition does not exeist
in one of the columns. Example: columns x, y & z where z doesn't contain
"this condition".

I can understand that these work:
=SUMPRODUCT((condition1)*(condition2)*(condition3) )
and even =SUMPRODUCT(--(condition1),--(condition2),--(condition3))
but I can't understand how to get to my desired results. Would I write
the formula like this?
=SUMPRODUCT((condition1)*(condition2)-(condition3))
=SUMPRODUCT(--(condition1),--(condition2),-(condition3))

TIA

Sumproduct Fucntion - the use of dash ( -- )
 

Ron Coderre wrote:
Can you give some more details about the
kind of test you want to perform on Col_Z?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"plb2862" wrote in message
...
I've been trying to sum multiple columns where a condition does not exeist
in one of the columns. Example: columns x, y & z where z doesn't contain
"this condition".

I can understand that these work:
=SUMPRODUCT((condition1)*(condition2)*(condition3) )
and even =SUMPRODUCT(--(condition1),--(condition2),--(condition3))
but I can't understand how to get to my desired results. Would I write
the formula like this?
=SUMPRODUCT((condition1)*(condition2)-(condition3))
=SUMPRODUCT(--(condition1),--(condition2),-(condition3))

TIA


Thanks for getting back to me. Yes, (condition 3) is not in column z.
TIA

Sumproduct Fucntion - the use of dash ( -- )
 

I'm sorry; I wasn't clear.

The SUMPRODUCT structure you'll need will
be dependent on the kind of data you are
testing and the kind of test you want to do.

Are we testing if each corresponding cell in Col_Z:
.. is a number?
.. is a specific number?
.. is text?
.. is specific text?
.. matches an item in a list?
Example:
Multiply each cell in X1:X100
by each corresponding cell in Y1:Y100
if the corresponding cell in Z1:Z100
does not equal "Canceled".

or..are we testing if any of the above
conditions exist anywhere in Col_Z?
Example:
Multiply each cell in X1:X100
by each corresponding cell in Y1:Y100
BUT only if "Canceled" does NOT exist
anywhere in Z1:Z100

In the meantime, perhaps this kind of structu
=SUMPRODUCT(--(Z1:Z100<"CANCELED"),--(X1:X100),--(Y1:Y100))


--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"plb2862" wrote in message
...
Ron Coderre wrote:
Can you give some more details about the
kind of test you want to perform on Col_Z?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"plb2862" wrote in message
...
I've been trying to sum multiple columns where a condition does not
exeist in one of the columns. Example: columns x, y & z where z doesn't
contain "this condition".

I can understand that these work:
=SUMPRODUCT((condition1)*(condition2)*(condition3) )
and even =SUMPRODUCT(--(condition1),--(condition2),--(condition3))
but I can't understand how to get to my desired results. Would I write
the formula like this?
=SUMPRODUCT((condition1)*(condition2)-(condition3))
=SUMPRODUCT(--(condition1),--(condition2),-(condition3))

TIA


Thanks for getting back to me. Yes, (condition 3) is not in column z.
TIA

Sumproduct Fucntion - the use of dash ( -- )
 

Ron Coderre wrote:
I'm sorry; I wasn't clear.

The SUMPRODUCT structure you'll need will
be dependent on the kind of data you are
testing and the kind of test you want to do.

Are we testing if each corresponding cell in Col_Z:
. is a number?
. is a specific number?
. is text?
. is specific text?
. matches an item in a list?
Example:
Multiply each cell in X1:X100
by each corresponding cell in Y1:Y100
if the corresponding cell in Z1:Z100
does not equal "Canceled".

or..are we testing if any of the above
conditions exist anywhere in Col_Z?
Example:
Multiply each cell in X1:X100
by each corresponding cell in Y1:Y100
BUT only if "Canceled" does NOT exist
anywhere in Z1:Z100

In the meantime, perhaps this kind of structu
=SUMPRODUCT(--(Z1:Z100<"CANCELED"),--(X1:X100),--(Y1:Y100))


--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"plb2862" wrote in message
...
Ron Coderre wrote:
Can you give some more details about the
kind of test you want to perform on Col_Z?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"plb2862" wrote in message
...
I've been trying to sum multiple columns where a condition does not
exeist in one of the columns. Example: columns x, y & z where z doesn't
contain "this condition".

I can understand that these work:
=SUMPRODUCT((condition1)*(condition2)*(condition3) )
and even =SUMPRODUCT(--(condition1),--(condition2),--(condition3))
but I can't understand how to get to my desired results. Would I write
the formula like this?
=SUMPRODUCT((condition1)*(condition2)-(condition3))
=SUMPRODUCT(--(condition1),--(condition2),-(condition3))

TIA

Thanks for getting back to me. Yes, (condition 3) is not in column z.
TIA



Thanks again, (condition 3) would be specific text to exclude the record.