SOLVED

Does the union operator exist?

%3CLINGO-SUB%20id%3D%22lingo-sub-2590110%22%20slang%3D%22en-US%22%3EDoes%20the%20union%20operator%20exist%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2590110%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20heard%20union%20operator%20in%20Excel%20previously.%20But%20I%20would%20like%20to%20know%20if%20it%20really%20exists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20Excel%20for%20Windows%2C%20where%20%60%2C%60%20(comma)%20is%20an%20argument%20separator%2C%20the%20formula%20%60%3DSUM((A1%2CF6))%60%20works%3B%20it%20does%20sum%20up%20A1%20and%20F6.%20But%2C%20should%20we%20consider%20the%20comma%20as%20a%20union%20operator%20or%20a%20separator%20of%202%20arguments%20of%20the%20SUM%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20a%20formal%20justification%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2590110%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2590276%22%20slang%3D%22en-US%22%3ERe%3A%20Does%20the%20union%20operator%20exist%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2590276%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F113332%22%20target%3D%22_blank%22%3E%40Tie%20CHENG%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EYes%2C%20the%20union%20operator%20does%20exist.%20The%20SUM%20function%20is%20not%20a%20very%20good%20example%2C%20since%20its%20syntax%20is%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUM(range1%2C%20range2%2C%20range3%2C%20...)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eso%20comma%20acts%20as%20argument%20separator.%20Using%20double%20parentheses%20does%20force%20Excel%20to%20treat%20the%20comma%20as%20union%20separator%2C%20but%20the%20effect%20is%20exactly%20the%20same%20as%20using%20single%20parantheses.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20example%20where%20comma%20is%20really%20used%20as%20union%20operator%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DLARGE((A1%3AC3%2CD4%3AE5)%2C2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20LARGE%20function%20has%20only%202%20arguments%3A%20%3DLARGE(array%2C%20k).%20If%20you%20try%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DLARGE(A1%3AC3%2CD4%3AE5%2C2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyou'll%20get%20an%20error%20since%20you%20specified%20too%20many%20arguments.%20In%20(A1%3AC3%2CD4%3AE5)%2C%20the%20comma%20creates%20the%20union%20of%20A1%3AC3%20and%20D4%3AE5.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20keep%20in%20mind%20that%20%2C%20is%20not%20a%20REAL%20union%20operator%3A%20if%20the%20ranges%20intersect%2C%20the%20intersection%20will%20be%20counted%20twice.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20A1%3D1%20and%20A2%3D2.%3C%2FP%3E%0A%3CP%3EBoth%20%3DSUM(A1%3AA2%2CA1%3AA2)%20and%20%3DSUM((A1%3AA2%2CA1%3AA2))%20will%20return%206.%20If%20(A1%3AA2%2CA1%3AA2)%20was%20a%20real%20union%2C%20you'd%20expect%20the%20second%20formula%20to%20return%203%2C%20not%206.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

 

I have heard union operator in Excel previously. But I would like to know if it really exists.

 

In my Excel for Windows, where `,` (comma) is an argument separator, the formula `=SUM((A1,F6))` works; it does sum up A1 and F6. But, should we consider the comma as a union operator or a separator of 2 arguments of the SUM function?

 

Does anyone have a formal justification?

 

Cheers

1 Reply
best response confirmed by Tie CHENG (Occasional Contributor)
Solution

@Tie CHENG

Yes, the union operator does exist. The SUM function is not a very good example, since its syntax is

 

=SUM(range1, range2, range3, ...)

 

so comma acts as argument separator. Using double parentheses does force Excel to treat the comma as union separator, but the effect is exactly the same as using single parentheses.

 

Here is an example where comma is really used as union operator:

 

=LARGE((A1:C3,D4:E5),2)

 

The LARGE function has only 2 arguments: =LARGE(array, k). If you try

 

=LARGE(A1:C3,D4:E5,2)

 

you'll get an error since you specified too many arguments. In (A1:C3,D4:E5), the comma creates the union of A1:C3 and D4:E5.

 

But keep in mind that , is not a REAL union operator: if the ranges intersect, the intersection will be counted twice.

 

Let's say A1=1 and A2=2.

Both =SUM(A1:A2,A1:A2) and =SUM((A1:A2,A1:A2)) will return 6. If (A1:A2,A1:A2) was a real union, you'd expect the second formula to return 3, not 6.