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.

 

[Joe4]

I was just reading through this whole thread for the first time and came across this:

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.

For future reference, just wanted to let you know that while you cannot upload files to this site, there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.

Also, there is a Test Here forum on this board that you can use to test out these tools to make sure you have them working correctly before posting to your threads.

 

[shg]

I think what's important is that you understand how Excel works, and live with that.

We haven't been down the rathole that -2^2 returns 4 lately.

 

[jproffer]

I agree with the point of your post, Shg, as far as the Union/group/etc operator... but regarding the second part of your post.......I guess it was before my time here, but I can't believe that was ever an argument.

(-2 x -2) really is 4

Naturally it is possible that I don't understand the "^" operator...I thought it was "X to the power of Y".

 

[shg]

... but I can't believe that was ever an argument.

https://www.mrexcel.com/forum/excel-questions/579123-known-bug-excel-3-print.html?pp=40

 

[jproffer]

I see, I see...multiple ("stacked") powers. I'm not going to read it all, but I see now what they were arguing about at least

 

[mikerickson]

There are some non-Microsoft sites that claim that the Union operator is not the set theory operator, ...

Excel Union is not set theory union.
Set-Theory-Union does not error when Nothing is an argument.

 

[mikerickson]

On the VBA side, I found an odd difference between rngU and rngV below.

Dim rngA As Range, rngB As Range, rngU As Range, rngV As Range

Set rngA = Range("B1:B8")
Set rngB = Range("B5:B10")
Set rngU = Application.Union(rngA, rngB)

Set rngV = Range("B1:B8, B5:B10")

Debug.Print WorksheetFunction.Sum(rngA, rngB) ' 14

Debug.Print WorksheetFunction.Sum(rngU) ' 10

Debug.Print WorksheetFunction.Sum(rngV) ' 14

 

[JoeMo]

On the VBA side, I found an odd difference between rngU and rngV below.

Dim rngA As Range, rngB As Range, rngU As Range, rngV As Range

Set rngA = Range("B1:B8")
Set rngB = Range("B5:B10")
Set rngU = Application.Union(rngA, rngB)

Set rngV = Range("B1:B8, B5:B10")

Debug.Print WorksheetFunction.Sum(rngA, rngB) ' 14

Debug.Print WorksheetFunction.Sum(rngU) ' 10

Debug.Print WorksheetFunction.Sum(rngV) ' 14

Appears that rngV is seen as having two areas while the Union function knows that rngA and rngB overlap and form a single area. Note also the second way of setting rngV produces a single area and a sum of 10 (when all relevant cells contain a 1).

Sub UnionTest()
Set rnga = Range("B1:B8")
Set rngb = Range("B5:B10")
Set rngU = Application.Union(rnga, rngb)
MsgBox rngU.Areas.Count & vbLf & Application.Sum(rngU)
Set rngv = Range("B1:B8, B5:B10")
MsgBox rngv.Areas.Count & vbLf & Application.Sum(rngv)
Set rngv = Range(rnga, rngb)
MsgBox rngv.Areas.Count & vbLf & Application.Sum(rngv)
End Sub

 

[mikerickson]

As the string argument of Range or in a worksheet formula, it looks like a comma is the "add area" operator rather than "union".

 

[footoo]

Here's a UDF :

Public Function USUM(rng As Range)
Dim area As Range, uRng As Range
For Each area In rng.Areas
    If uRng Is Nothing Then
        Set uRng = area
    Else
        Set uRng = Application.Union(uRng, area)
    End If
Next
USUM = Application.WorksheetFunction.Sum(uRng)
End Function

Enter on the worksheet like this :
=USUM((K1:K6,K5:K11))