Home |
Search |
Today's Posts |
#1
|
|||
|
|||
double dash in formulas --
Sometimes I'll see a dash-dash used in a formula like -
--trim or --left What does that double-dash do when it's infront of the function, and why use '--trim' instead of just 'trim'? Ted |
#2
|
|||
|
|||
Hi Ted,
See JE McGoimpsey's explanation at: http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Norman "Ted Metro" wrote in message ... Sometimes I'll see a dash-dash used in a formula like - --trim or --left What does that double-dash do when it's infront of the function, and why use '--trim' instead of just 'trim'? Ted |
#3
|
|||
|
|||
=Trim() will return a string (not a number). (Same with =right(), =left(),
=mid()). So if you had 1234ASDF in a cell and wanted to extract the character 1, you could use: =left(a1,1) But this formula returns the string '1 (like entering a value with a leading apostrophe). One way to convert that string 1 to a number 1 is to multiply by 1. =left(a1,1)*1 or add 0 =left(a1,1)+0 Or even using -- =--left(a1,1) The rightmost minus would convert it to -1 and the left minus would change the sign back to positive. === You may have seen this in formulas like: =sumproduct(--(a1:a10="hi"),--(b1:b10="there")) In this case, the right minus converts True/Falses to -1/0's and the left minus converts the -1/0 to +1/0. (and sumproduct likes to work with numbers--not true/false's.) Ted Metro wrote: Sometimes I'll see a dash-dash used in a formula like - --trim or --left What does that double-dash do when it's infront of the function, and why use '--trim' instead of just 'trim'? Ted -- Dave Peterson |
#4
|
|||
|
|||
This technique was popularized by Harlan Grove in
converting arrays of booleans to 1's and 0's when working with arrays in formulas. I think it's best explained through an example. Imagine this list in A1:A5: eat easy tree elephant car We want to count the number of words beginning with the letter "e". We could use the formula: =SUMPRODUCT(--(LEFT(A1:A5)="e")) This formula evaluates the first letter of each cell in the range and equates it to "e". If you select that portion of the formula and press F9, you would see: =SUMPRODUCT(--({TRUE;TRUE;FALSE;TRUE;FALSE})) Unfortunately, we can't sum these booleans in this way, so we use the double dash (actually it's referred to as double unary I believe) to convert the booleans first to all negatives: =SUMPRODUCT(-{-1;-1;0;-1;0}) and the second one to convert them back to positive. The 0's are not affected. So you end up with: =SUMPRODUCT({1;1;0;1;0}) which equals 3. It should be noted that there are other methods. You can also multiply by 1, add 0, use the power of 1, or use the N function to convert the array of booleans. =SUMPRODUCT(1*(LEFT(A1:A5)="e")) =SUMPRODUCT(0+(LEFT(A1:A5)="e")) =SUMPRODUCT(N(LEFT(A1:A5)="e")) =SUMPRODUCT((LEFT(A1:A5)="e")^1) Determining which formula is most efficient of all of these is an argument for another day. HTH Jason Atlanta, GA -----Original Message----- Sometimes I'll see a dash-dash used in a formula like - --trim or --left What does that double-dash do when it's infront of the function, and why use '--trim' instead of just 'trim'? Ted . |
#5
|
|||
|
|||
This is a neat trick!!!!!
Dave Peterson wrote: =Trim() will return a string (not a number). (Same with =right(), =left(), =mid()). So if you had 1234ASDF in a cell and wanted to extract the character 1, you could use: =left(a1,1) But this formula returns the string '1 (like entering a value with a leading apostrophe). One way to convert that string 1 to a number 1 is to multiply by 1. =left(a1,1)*1 or add 0 =left(a1,1)+0 Or even using -- =--left(a1,1) The rightmost minus would convert it to -1 and the left minus would change the sign back to positive. === You may have seen this in formulas like: =sumproduct(--(a1:a10="hi"),--(b1:b10="there")) In this case, the right minus converts True/Falses to -1/0's and the left minus converts the -1/0 to +1/0. (and sumproduct likes to work with numbers--not true/false's.) Ted Metro wrote: Sometimes I'll see a dash-dash used in a formula like - --trim or --left What does that double-dash do when it's infront of the function, and why use '--trim' instead of just 'trim'? Ted |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't formulas obey vertical alignment commands? | Excel Discussion (Misc queries) | |||
Formulas List | Excel Discussion (Misc queries) | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) | |||
when displaying formulas, how to start a new line in the same cel. | Excel Discussion (Misc queries) |