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.

 

[JenniferMurphy]

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

There are some non-Microsoft sites that claim that the Union operator is not the set theory operator, but I couldn't find any from Microsoft. Can you? If the comma is being interpreted as an argument delimiter, then we aren't seeing the Union operator in action.

This is supported by the website I referenced above that shows what happens when the same expression is typed into the Name Box. Did you read that? That seems like stronger evidence that the Union operator actually is working according to set theory, but we just can't use it in function calls because the geniuses at M$FT chose the comma instead of some other symbol, like "|".

 

[footoo]

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

Typing cell refs into the Name Box is for the purpose of selecting cells - and it is not possible to select a cell more than once.
The tallies at the bottom right are for the selected cells.

 

[JenniferMurphy]

Typing cell refs into the Name Box is for the purpose of selecting cells - and it is not possible to select a cell more than once.
The tallies at the bottom right are for the selected cells.

Perhaps. But the fundamental question remains unanswered. How can we show for certain that the Union operator is actually getting executed?

In this expression, it's not clear (to me) whether the comma is the Union operator or just separating arguments. I kinda think the parser code would separate the arguments first.

=sum(K1:K6,K5:K11)

If so, then it is doing two separate sums and then summing the results.

It would seem that this expression should call the Union operator, no?

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

The code inside the inner parens should get executed before the result is passed to the SUM function, so that comma can't be an argument delimiter. It has to be the Union operator. Correct?

That should support your position.

But either way, it's a serious design flaw in my opinion.

 

[footoo]

The code inside the inner parens should get executed before the result is passed to the SUM function, so that comma can't be an argument delimiter. It has to be the Union operator. Correct?

It would be nice if this were so - but it's not.
Tetra201 pointed out earlier : “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?”
I don’t know of an example.

I really cannot understand why you are having such a problem with this.
Is it not just a matter of accepting that the comma does not produce a set theory union and that your formula needs to written accordingly?
(If A1 contains 2, what would you want returned from =SUM(2,A1,2) ? 6 or 2 ?)

 

[Rick Rothstein]

The code inside the inner parens should get executed before the result is passed to the SUM function, so that comma can't be an argument delimiter. It has to be the Union operator. Correct?

It would be nice if this were so - but it's not.

Actually, I am pretty sure it is. I am almost positive tht parentheses that are not part a function's syntax forms an expression to be evaluated so in the case of two ranges separated by a comma and contained within a set of parentheses that are not part of a function's syntax form a union of the two ranges. Now, it is true that this "union" is what Excel considers a union and not the set theory definition of a union, but that comma in that circumstance would have to be an Excel union operator.

 

[footoo]

parentheses that are not part a function's syntax forms an expression to be evaluated so in the case of two ranges separated by a comma and contained within a set of parentheses that are not part of a function's syntax form a union of the two ranges. Now, it is true that this "union" is what Excel considers a union and not the set theory definition of a union, but that comma in that circumstance would have to be an Excel union operator.

That's the point I was trying to make - you state it so much better.

 

[JenniferMurphy]

I really cannot understand why you are having such a problem with this.

Because it's wrong. One way or another, they screwed up.

Is it not just a matter of accepting that the comma does not produce a set theory union and that your formula needs to written accordingly?

I have no choice but to accept that it is the way it is, like a million other screwball and outright dumb things about M$FT products. Woody Leonhard wrote a 900-page book delineating the idiotic bugs in Word, many of which are still uncorrected today, 20 or so years later.

(If A1 contains 2, what would you want returned from =SUM(2,A1,2) ? 6 or 2 ?)

This is completely irrelevant. The first and third arguments are literals, not ranges, so there is nothing to join. Try typing "2,A1,2" into the Name Box. You will get an error telling you that you have entered an invalid reference.

The sum is, of course, 6. It is equivalent to "2 + A1 + 2" and it has nothing whatsoever to do with this discussion.

Conversely, the expression "=sum(k1:k6,k5:k11)" is equivalent to "=sum(k1:k6) + sum(K5:k11)"; whereas, the expression "=sum(k1:k6,k5:k11)" is equivalent to "=sum(union(k1:k6,k5:k11))", or so it appears.

Therefore, my position is now that M$FT screwed up twice. The Union operator does not work properly AND they stupidly chose the comma as the Union operator character.

 

[footoo]

Perhaps your criticisms/complaints can be summarized as follows:
For worksheet formulas, XL does not have a dedicated operator which combines range references into a union as defined by set theory.

I don’t think most people will think this is such a big deal.

 

[JenniferMurphy]

I don’t think most people will think this is such a big deal.

Yeah, well, something like 70% of people believe that magnetic therapy is scientific, 32% believe in lucky numbers, 25% of people believe in astrology, 25% believe that vaccines have been shown to cause autism, 20% believe that a UFO crashed in Roswell, New Mexico and the government is covering it up, and 15% believe that the government is putting mind-control technology in TV signals.

ymmv

 

[Rick Rothstein]

Yeah, well, something like 70% of people believe that magnetic therapy is scientific, 32% believe in lucky numbers, 25% of people believe in astrology, 25% believe that vaccines have been shown to cause autism, 20% believe that a UFO crashed in Roswell, New Mexico and the government is covering it up, and 15% believe that the government is putting mind-control technology in TV signals.

ymmv

WHAT!??!!!?! You mean those things are not true?

In all seriousness, as to your union question... I think what Microsoft is calling "creating a union" is nothing more than "creating a set".