| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| is there a memory plus (like a calculator) fucntion in excel? | Excel Worksheet Functions | |||
| understanding dash dash in a excel formula | Excel Worksheet Functions | |||
| Recording incidences of results of a RAND() fucntion. | Excel Discussion (Misc queries) | |||
| Sumif Fucntion | Excel Worksheet Functions | |||
| Sumif Fucntion | Excel Worksheet Functions | |||
Linear Mode
