Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
Just today I discovered that you can do some cool things w/
conditional sums of the form: sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) The above might give me sales in the Western region of Blue things (column c might have the sales figures). This appears to avoid having to enter arrary formulas (CTRL SHIFT ENTER as well as the likely different specification for the formula itself) and would seem to be a lot easier to work with. What I haven't seen documented other than quick allusions to it is exactly what the "--" is doing. Since "--" isn't easy to search for on the web, I thought somebody here might be able to describe or refer me to a documentation source online that describes what "--" (& wrapping w/ () ) is doing. Seems to be a shorthand way to indicate "do an array operation with this". Thx!, - TK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
In the case of boolean expressions (that return TRUE/FALSE), the dbl-neg
converts TRUE to 1 and FALSE to 0. Example: --ISNUMBER(56) converts TRUE to the number 1 --ISNUMBER("this is text") converts FALSE to the number 0 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. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Just today I discovered that you can do some cool things w/ conditional sums of the form: sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) The above might give me sales in the Western region of Blue things (column c might have the sales figures). This appears to avoid having to enter arrary formulas (CTRL SHIFT ENTER as well as the likely different specification for the formula itself) and would seem to be a lot easier to work with. What I haven't seen documented other than quick allusions to it is exactly what the "--" is doing. Since "--" isn't easy to search for on the web, I thought somebody here might be able to describe or refer me to a documentation source online that describes what "--" (& wrapping w/ () ) is doing. Seems to be a shorthand way to indicate "do an array operation with this". Thx!, - TK |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
so what does
sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) deferrer from sumproduct((A1:A1000="West")*(B1:B1000="Blue")*(C1 :C1000))? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
It's called a double unary. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Just today I discovered that you can do some cool things w/ conditional sums of the form: sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) The above might give me sales in the Western region of Blue things (column c might have the sales figures). This appears to avoid having to enter arrary formulas (CTRL SHIFT ENTER as well as the likely different specification for the formula itself) and would seem to be a lot easier to work with. What I haven't seen documented other than quick allusions to it is exactly what the "--" is doing. Since "--" isn't easy to search for on the web, I thought somebody here might be able to describe or refer me to a documentation source online that describes what "--" (& wrapping w/ () ) is doing. Seems to be a shorthand way to indicate "do an array operation with this". Thx!, - TK |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
On Feb 5, 12:33*pm, "Ron Coderre"
wrote: In the case of boolean expressions (that return TRUE/FALSE), the dbl-neg converts TRUE to 1 and FALSE to 0. Example: --ISNUMBER(56) converts TRUE to the number 1 --ISNUMBER("this is text") converts FALSE to the number 0 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. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) wrote in message ... Just today I discovered that you can do some cool things w/ conditional sums of the form: sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) The above might give me sales in the Western region of Blue things (column c might have the sales figures). *This appears to avoid having to enter arrary formulas (CTRL SHIFT ENTER as well as the likely different specification for the formula itself) and would seem to be a lot easier to work with. What I haven't seen documented other than quick allusions to it is exactly what the "--" is doing. *Since "--" isn't easy to search for on the web, I thought somebody here might be able to describe or refer me to a documentation source online that describes what "--" (& wrapping w/ () ) is doing. *Seems to be a shorthand way to indicate "do an array operation with this". Thx!, - TK- Hide quoted text - - Show quoted text - I'm glad I asked. Got it. It's so obvious now that I see what it is. Thanks for the lighting reply. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
Using the "--" is slightly more efficient.
Try this: A1 = west B1 = blue C1 = 100 Enter both of your sample formulas and they both return 100. Now, change C1 to "X" and see what happens. -- Biff Microsoft Excel MVP "Ollie4" wrote in message ... so what does sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) deferrer from sumproduct((A1:A1000="West")*(B1:B1000="Blue")*(C1 :C1000))? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
Imagine a table with
A1: Region B1: Colour C1: Amount A2: West B2: Blue C2: 100 now run both formulae against that data. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ollie4" wrote in message ... so what does sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) deferrer from sumproduct((A1:A1000="West")*(B1:B1000="Blue")*(C1 :C1000))? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
On Feb 5, 1:12*pm, Ollie4 wrote:
so what does sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) deferrer from sumproduct((A1:A1000="West")*(B1:B1000="Blue")*(C1 :C1000))? Related question: Why does the "*" work, but if you use "," instead you don't get the right answer. I thought sumproduct multiplied the entries so in a way, I thought they'd still get multiplied "the right way". PS, thanks for the link Bob Phillips. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
Wow, tnx for the link! I'm using sumproduct for allmost everything for
the last years, but never knew the -- trick. Great! Tnx! Now i dont have to validate the data first. On 5 feb, 22:13, "Bob Phillips" wrote: It's called a double unary. Seehttp://www.xldynamic.com/source/xld.SUMPRODUCT.htmlfor a detailed explanation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... Just today I discovered that you can do some cool things w/ conditional sums of the form: sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) The above might give me sales in the Western region of Blue things (column c might have the sales figures). This appears to avoid having to enter arrary formulas (CTRL SHIFT ENTER as well as the likely different specification for the formula itself) and would seem to be a lot easier to work with. What I haven't seen documented other than quick allusions to it is exactly what the "--" is doing. Since "--" isn't easy to search for on the web, I thought somebody here might be able to describe or refer me to a documentation source online that describes what "--" (& wrapping w/ () ) is doing. Seems to be a shorthand way to indicate "do an array operation with this". Thx!, - TK |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using "--" as an array formula shortcut / alternative
* works where comma doesn't when there are conditions. So
=SUMPRODUCT(A1:A10,B1:B10) works fine when they are all numbers. But if you want to test for A1:A10 10 say the test A1:A1010 returns TRUE FALSE which have to be transformed into numbers for SUMPRODUCT to work. One way is to multiply the arrays (TRUE*1=1, FALSE*1=0), another is to double negate it (--TRUE), another is to add 0 (TRUE+0), another is to raise to the 0th power (TRUE^2). All of these have the effect of transforming to a number which SUMPRODUCT is happier to work with. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... On Feb 5, 1:12 pm, Ollie4 wrote: so what does sumproduct(--(A1:A1000="West"),--(B1:B1000="Blue"),C1:C1000) deferrer from sumproduct((A1:A1000="West")*(B1:B1000="Blue")*(C1 :C1000))? Related question: Why does the "*" work, but if you use "," instead you don't get the right answer. I thought sumproduct multiplied the entries so in a way, I thought they'd still get multiplied "the right way". PS, thanks for the link Bob Phillips. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Count the number of "rows" (or Array items) included in a Sumif formula? | Excel Discussion (Misc queries) | |||
"Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
How do I use "offset" function in "array formula"? | Excel Discussion (Misc queries) | |||
Shortcut key for "Paste Options" and "Error Checking" buttons? | Excel Discussion (Misc queries) | |||
Excel shortcut key "Cut" Ctrl+X come up a formula to me why? | Excel Discussion (Misc queries) |