Jul 27 2021 02:45 AM
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
Jul 27 2021 03:34 AM - edited Jul 30 2021 06:37 AM
SolutionYes, 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.
Jul 27 2021 03:34 AM - edited Jul 30 2021 06:37 AM
SolutionYes, 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.