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.

 

[JoeMo]

I don't download files so can't look at your example, but I am curious, so I tried a simple example of creating a union of two overlapping ranges where both ranges, K1:K6 and K5:K11, had the number 1 in all their cells. The sum function returns 11 which is correct. Is this not what you mean when referring to overlapping ranges? Can you post a simple example that illustrates the flaw you have found?

Sub SimpleOverlapUnion()
Dim r As Range
Set r = Union(Range("K1:K6"), Range("K5:K11"))
MsgBox r.Address & vbLf & r.Areas.Count & vbLf & Application.Sum(r)
End Sub

 

[Rick Rothstein]

I don't download files so can't look at your example, but I am curious, so I tried a simple example of creating a union of two overlapping ranges where both ranges, K1:K6 and K5:K11, had the number 1 in all their cells. The sum function returns 11 which is correct.

SUM(K1:K6,K5:K11) returns 13 for me.

As far as I know, Excel does not "fuse" overlapping areas... each area in a unionized range is treated independently (the same for a non-contiguous range where there are overlapped cells within the non-contiguous areas).

 

[Tetra201]

IMO, Jennifer is talking about

=SUM(K1:K6,K5:K11)

that returns 13.

 

[JoeMo]

SUM(K1:K6,K5:K11) returns 13 for me.

As far as I know, Excel does not "fuse" overlapping areas... each area in a unionized range is treated independently (the same for a non-contiguous range where there are overlapped cells within the non-contiguous areas).

SUM(K1:K6,K5:K11) returns 13 for me too. I thought the OP was saying there's a flaw in the VBA Union operator, but I suppose that's not the case. If you run the simple sub I posted the sum of the Union of the two ranges returns 11 if all cells contain a 1.

 

[JenniferMurphy]

I don't download files so can't look at your example,

If this board allowed attachments, I would have uploaded it here. But maybe you wouldn't look at that either. If this board allowed me to paste images, I would have done that.

but I am curious, so I tried a simple example of creating a union of two overlapping ranges where both ranges, K1:K6 and K5:K11, had the number 1 in all their cells. The sum function returns 11 which is correct. Is this not what you mean when referring to overlapping ranges? Can you post a simple example that illustrates the flaw you have found?

Since you didn't post your worksheet code, so I can't tell what you are doing. But using your example, I would expect these results:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Formula[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]Comments[/TD]
[/TR]
[TR]
[TD]=sum(K1:K11)[/TD]
[TD="align: center"]11[/TD]
[TD]Sum of overall area[/TD]
[/TR]
[TR]
[TD]=sum(K1:K6)[/TD]
[TD="align: center"]6[/TD]
[TD]Sum of Range A[/TD]
[/TR]
[TR]
[TD]=sum(K5:K11)[/TD]
[TD="align: center"]7[/TD]
[TD]Sum of Range B[/TD]
[/TR]
[TR]
[TD]=sum(K1:K6 K5:K11)[/TD]
[TD="align: center"]2[/TD]
[TD]Sum of intersection (correct)[/TD]
[/TR]
[TR]
[TD]=sum(K1:K6,K5:K11)[/TD]
[TD="align: center"]13[/TD]
[TD]Sum of union (incorrect)[/TD]
[/TR]
</tbody>[/TABLE]
In the last example, where the design flaw is, cells K5 & K6 get counted twice.

 

[JenniferMurphy]

SUM(K1:K6,K5:K11) returns 13 for me.

Me, too

As far as I know, Excel does not "fuse" overlapping areas... each area in a unionized range is treated independently (the same for a non-contiguous range where there are overlapped cells within the non-contiguous areas).

I'm OK with that behavior as long as they don't call it a "Union". The union of K1:K6 & K5:K11 is K1:K11.

 

[JenniferMurphy]

I thought the OP was saying there's a flaw in the VBA Union operator, but I suppose that's not the case.

I don't recall mentioning VBA...

If you run the simple sub I posted the sum of the Union of the two ranges returns 11 if all cells contain a 1.

I don't run simple subs, but if the VBA Union operator returns 11, then it seems that the VBA programmers have better math skills than the workbook programmers, which is actually not that surprising.

 

[Rick Rothstein]

I'm OK with that behavior as long as they don't call it a "Union". The union of K1:K6 & K5:K11 is K1:K11.

They probably called it "union" because, normally, one would not be specifying overlapping ranges when doing things with multiple ranges.

I am guessing that for the SUM function you want to do, this formula might be what should be used...

=SUM(Rng1,Rng2)-IFERROR(SUM(Rng1 Rng2),0)

Note: That is a space (the intersect operator in Excel) between the two ranges in the second SUM function call.

 

[JenniferMurphy]

They probably called it "union" because, normally, one would not be specifying overlapping ranges when doing things with multiple ranges.

Or, more likely, because they were lazy or mathematically challenged. The term "union", especially when paired with "intersect" (space operator), already has a well-defined and long-standing definition in set theory. Not adhering to that standard is just plain incompetence. At least the VBA guys appear to have gotten it right.

And for the worksheet version of a function to work differently from the VBA version is another whole level of incompetence and failure of the left hand to know what the right hand is doing. This is ridiculous.

I am guessing that for the SUM function you want to do, this formula might be what should be used...

=SUM(Rng1,Rng2)-IFERROR(SUM(Rng1 Rng2),0)

You are one step ahead of me. I was going to try to remember to use

=SUM(Rng1,Rng2)-SUM(Rng1 Rng2)

But I hadn't tested what happens of there is no overlap (null intersection). (sigh)