Major design flaw in Union "operator"

[JenniferMurphy]

I just discovered what I consider a major flaw in the design of the Union (comma) operator. As illustrated in the workbook here:

https://www.dropbox.com/sh/v0799qr2uex4imf/AAAWi4Jhq4-vfCmuQTn4qo4Za?dl=0

The Sum function, when applied to a union of overlapping ranges, will sum the values in the overlapping area multiple times. Mathematically, this is nonsense. What possible rationale could the geniuses at M$FT possibly have had in mind?

I am inclined to agree with opinions expressed here,

http://dailydoseofexcel.com/archives/2005/01/16/union-and-intersect/

that they just had this "operator", the comma, already implemented and decided to call it an operator and ignore the fact that it is mathematically wrong.

I would love to hear a compelling defense of this design.

PS: It looks like a couple of my personal UDFs, which I moved to a code module, may not be working in the workbook. My apologies if that's true. I would also appreciate any hints of how I can make that work when I send workbooks to other people that make use of any of my personal add-in code.

 

[mikerickson]

The comma in =SUM(K1:K6,K5:K11) is seperating two arguments of SUM, not creating a union of ranges

SUM(x, y) = SUM(x) + SUM so it is returning the correct value.

 

[Rick Rothstein]

The comma in =SUM(K1:K6,K5:K11) is seperating two arguments of SUM, not creating a union of ranges

That may be true, but still, this does create a union and it will returns 13 as well...

=SUM((K1:K6,K5:K11))

I have often wondered when a function allows for multiple range arguments whether the comma is acting as a separator or as a union operator given the end result appears to be the same.

 

[JenniferMurphy]

The comma in =SUM(K1:K6,K5:K11) is seperating two arguments of SUM, not creating a union of ranges

SUM(x, y) = SUM(x) + SUM so it is returning the correct value.

Aha! Of course. Good catch.

So the design flaw is different from what I initially thought. It's that they choose to use the same symbol, the comma, for a set operator, that already had a function as an argument delimiter. I actually think that dumber than the error I thought it was. It doesn't require any knowledge of set theory -- just of their own syntax.

So is there any way to get the actual Union operator to work? I tried

=SUM((K1:K6,K5:K11))

but got the same sum.

 

[Tetra201]

To me, the question is: is there an example where the comma in (K1:K6,K5:K11) acts as a true union operator, and not as a separator?

 

[JenniferMurphy]

To me, the question is: is there an example where the comma in (K1:K6,K5:K11) acts as a true union operator, and not as a separator?

Exactly. So, is there?

I tried

=COUNT(K1:K6,K5:K11)
=COUNT((K1:K6,K5:K11))

They both return 13.

 

[JenniferMurphy]

In this thread from 2009, someone asks why the arguments in his function calls are separated by semicolons, instead of commas.

https://www.mrexcel.com/forum/excel-questions/385317-semicolon-comma-regional.html

The answer is that it is needed because the comma, not the period, is the decimal separator in many countries. I am reluctant to mess with the regional settings because it affects all programs, but if I were to change the decimal separator to a comma, which appears to make the argument separator the semicolon, would the comma then become the Union operator. Naw, probably not because it would then be confused with the decimal separator.

 

[footoo]

There is no Excel operator that produces a union like a set theory union.

 

[JenniferMurphy]

I just learned something from this page:

http://www.excel-first.com/excel-reference-operators/

If I type "K1:K6,K5:K11" into the Name Box, the tallies at the bottom right of the sheet show:

Average: 1 Count: 11 Sum: 11

This would seem to indicate that the Union operation is working properly. The problem is that we can't get to is because the argument separator gets executed first. It wold seem that enclosing the whole argument in parens should do the trick, but it doesn't seem to.

=sum((K1:K6,K5:K11))

 

[JenniferMurphy]

There is no Excel operator that produces a union like a set theory union.

How do you know that? What is that based on? Do you have a reference in M$FT literature somewhere?

My experiment above with the Name Box seems to suggest otherwise.

 

[footoo]

How do you know that? What is that based on? Do you have a reference in M$FT literature somewhere?

My experiment above with the Name Box seems to suggest otherwise.

Plenty of lists available. For example : https://www.google.com.hk/search?q=....69i57j0l5.23467j0j8&sourceid=chrome&ie=UTF-8