A couple of rarely used features of worksheet formulas are Unions and Intersects.
Unions
Most Excel users have probably have come across Union – the combination of ranges to be treated as one range. eg. =SUM(A1, A5:A11, C10)
If you start by typing the formula =SUM( then hold down CTRL and select ranges, Excel automatically defines a Union for you.
The ranges are separated by a comma. The comma is referred to as a Union operator.
I’ve seen Unions used for a Grand Total by summing Sub Totals
Intersects
Just like the Union operator is a comma, the Intersect operator is a space.
When you separate ranges with a space then the range used is where they intersect. eg. =A1:C1 B1:B3 will return the same result as =B1
I’ve never seen Intersects used in the wild. I’ve only recently discovered it myself.
It’s interesting to note that in this sample changing Feb to Jan =SUM((Apple, Banana) Jan) does not result in a Circular Reference.
I knew about the intersect and union operators, but I didn’t know you could nest them!
I can’t help but feel there is hidden potential with intersects. It looks very similar to a lookup.
The Operator Precedence orders Intersect before Union which is why I used a bracket.
when i saw this, i started to thing about using Intersect with row and coloum, could be slow though.
Great!
Intersect by a blank separator is new to me. I’ll definitly use it.
Ola
This is also new to me: http://www.nsl.com/k/excel.k
First, nice & useful blog – thanks!
Second, I’m not convinced the union operator is really an operator and not just a piece of microsoft rhetoric. I’ve never seen it used in a context where it couldn’t ALSO be interpreted as a comma separating arguments (e.g., in a SUM function). For example, the following array formula, which uses an intersect operator, works:
{SUM(IF((A1:A10 A1:10)=”a”,1,0))}
whereas this one, which uses the so-called “union operator” returns a #VALUE! error
{SUM(IF((A1:A5,A6:10)=”a”,1,0))}
This is enourmously frustrating because an authentic union operator could be extremely useful when doing, e.g., complex database lookups.
Jay,
Thanks Jay – nice spotting
=A1:A5, A8:A10 is valid, but always returns #VALUE!
A pseudo-union.
Rob
This looks like it has a lot of potential for me. I have created named ranges which represent columns of unique parameters, and named ranges which represent groups of rows to partition the source of the data. What I would like to do is copy the several cells which are the interseection of the parameter range with a particular group of rows. I.e. column AB is named param1 and rows 10 to 20 are named datasetA and are several readings I want to do some computing on. What I have been experimenting with, without success, is using something like:
intersect(range(“param1?),range(“datasetA”)).copy worksheets(“sheet2?).range(“A1?)
Suggestions?
Thanks, Jim.
This is an informative page, with almost maliciously incorrect terminology.
The [space] character is, indeed, an “intersect operator.”
The [comma] character is IN NO WAY a mathematical “union operator.” It is in fact “set addition,” which SQL calls “union all.”
A union returns a SET (in this case, a SET of cells). Set Addition returns a MULTISET. The comma is Set Addition, because using Comma with the same cell twice returns TWO (of the same) cell. For instance:
3=COUNT(C2,C2,C2)=COUNT((C2,C2,C2))
If comma were really a union operator, these counts would return 1!
A less useless example is taking a “plus” shaped range:
6=COUNT(F4:H4,G3:G5)
Obviously, that plus-shaped range has 5 cells. Cell G4 is counted twice, PRECISELY because the comma-character is NOT a union operator.
How sad, that Excel does not support mathematical union (without using VBA).
-b
Benny
You may be correct, but Union is the label as assigned by Microsoft.
ptgIsect: Intersection (ptg=0Fh)
Computes the intersection of the top two operands. This is the Excel space operator.
ptgUnion: Union (ptg=10h)
Computes the union of the top two operands. This is the Excel comma operator.
ptgRange: Range (ptg=11h)
Computes the minimal bounding rectangle of the top two operands. This is the Excel colon operator.
So, if we define a named range NotFirst =Sheet1!$2:$65536,
then we can use a formula in cell A1 like this
=SUM((C:C) NotFirst)
to sum the whole of column C without getting a circular reference.
It’s a shame that there is no inverse of a range operator in Excel.
Something that would let me specify =NOT(A1,A:A) and return A2:A65536.
No clever comments about the number of rows in 2007, thanks.
Ed: Check the SubtractFirstPrinciples function at http://www.dailydoseofexcel.com/archives/2007/08/17/two-new-range-functions-union-and-subtract/
You can use it as in =SUM(SubtractFirstPrinciples(A:A,A1)) though it is quite slow if you give it an entire column in 2007.
hi,
in that case if comma is a union operator, how would you do a UNION of multiple non-adjacent holiday date ranges (rng1,rng2,rng3) and return a single range “Rng”, which then can be feeded to a workday function? (i am not talking VBA but excel worksheet formulae).
Rng = (Rng1,Rng2,Rng3)
=workday(startdate,no,Rng)
noname: Maybe
Specify a dis-contiguous range for an Excel function argument
http://www.tushar-mehta.com/excel/tips/multi_area_argument.html
tushar: multi-area ranges won’t work in WORKDAY.
noname: instead of (Rng1,Rng2,Rng3) you can try CHOOSE({1,2,3},Rng1,Rng2,Rng3) in the third argument. This assumes the ranges are single cells or single column ranges of equal size. If column sizes are unequal use IFERROR(…,0) to ignore any errors.