+ Reply to Thread
Results 1 to 10 of 10

Create a fully proportional chart of 3 sets and their areas of overlap (2007)

  1. #1
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Create a fully proportional chart of 3 sets and their areas of overlap (2007)

    Hi Everyone,

    [note: subsequent to submitting this original post, I removed the words "Venn Diagram" from the thread title, and removed confusing language from the text below.]

    I would like to chart some overlapping data.

    Quick data summary -- skip to next para if you like: I sell 3 types of clothing: pants, shirts and sweaters. And I have sales data on a specific group of clothing buyers: teenagers. Specifically, I know how many teens bought pants only, or bought shirts only, or pants + shirts only, etc..,etc..I would like to chart this data on teen buyers, and a Venn diagram comes to mind.

    However, a 3-circle Venn diagram *can not* be proportional across all 6 of its spaces -- at least not if it is made with perfect circles (see post below for explanation).

    So I'm wondering: is there a better way -- *preferably* a highly automated way -- to chart 3 overlapping sets. Of course, the great thing about a Venn diagram is that they eye instantly recognizes and comprehends the overlap areas - something a bar chart or similar just doesn't achieve.

    What else might work? Ideas, anyone?

    Cheers,

    Jay
    Last edited by JayUSA; 01-24-2010 at 01:22 PM. Reason: Clarification; removal of "Venn Diagram" from title

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Chart a proportional Venn Diagram or its equivalent (2007)

    Hi, maybe play around with a bubble chart? Then you can make the bubbles different sizes and position them to overlap to your liking.

    See attached for a suggestion.
    Attached Files Attached Files

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,428

    Re: Chart a proportional Venn Diagram or its equivalent (2007)

    This will give you layouts which you can populate.

    http://www.andypope.info/ngs/ng9.htm
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Chart a proportional Venn Diagram or its equivalent (2007)

    Thanks guys... but I don't think that this will meet my need.

    The problem is that my original title for this thread contradicts itself: there is no such thing as a fully proportional Venn Diagram (constructed with circles or any other perfect shape).

    To understand what I mean, think of two overlapping sets, A and B, as two overlapping perfect circles. Now, set C has some members that fall into the overlap of A+B, creating a space "within" A+B that could be called A+B+C.

    But, C has *no* members that overlap exclusively with A or exclusively with B, meaning that A+C and B+C are null sets.

    If C is not entirely contained within A+B+C, then C cannot be represented by a Venn Diagram using any perfect shape (at least not in 2 dimensions).

    So... perhaps I should have asked for a recommended chart type that does a good job at visually presenting sets and their overlap areas.

    Off the top of my head, the only type of chart that I can think of is a "colored tile" area chart, where every pure set and every overlap set is represented by a different number of different color tiles. Because all the tiles of any particular color can be arranged into a non-perfect shape, proportionality can be maintained across all types of overlap areas.

    If you know anything about this kind of "colored tile" area chart, or any other chart that could do a good job (therefore excluding bar and column charts), please let me know.

    Cheers,

    Jay


    PS: If possible I am going to remove the words "Venn Diagram" from this thread title as the words point only toward a non-workable solution.
    Last edited by JayUSA; 01-24-2010 at 01:27 PM.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Create a fully proportional chart of 3 sets and their areas of overlap (2007)

    There's no implicit assumption that any section of a Venn diagram is non-null. There is in an Euler diagram.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Create a fully proportional chart of 3 sets and their areas of overlap (2007)

    Hi Everyone,

    Perhaps there is a semantic or conceptual issue that is preventing me from presenting a cogent problem...

    I believe that:

    1) A proportional Venn Diagram made with perfect (ie. un-deformed shape) circles (or any other perfect shape) cannot represent all the possible, legitimate relationships between 3 sets. Yes -- you can fill a classic Venn diagram with Roman numerals, and it can communicate any combination of relationships between 3 sets. But if you want the *area* of each sector to be proportional to the number of members that it contains, there are specific relationship combos (as described in my earlier post) that cannot be described with a Venn diagram using perfect shapes.

    If that's true... what's the next best thing?

    The answer -- perhaps deceptively "easy" seeming to me -- is to throw out the requirement that the totality of each set be represented by a perfect shape. A more flexible approach might be to assign a perfect shape (square or triangle or pentagon -- not circle) to each *set member* Then, by assembling these shapes into a "mosaic", you can enlarge and reduce overlap sectors *independently* of each other, and all possible set relationships can be represented.

    To that end, I have seen this type of "mosaic chart" before. It appears most frequently (in the US) as a map of the 50 states made from 437 identical little boxes, with each box representing one vote in the US House of Representatives. On this map, New York state appears super-sized because it has a lot of votes in Congress, and North Dakota, with just one tiny box, practically disappears into oblivion. Now, the reason I think of this map is that it has inherent ability to support overlap sets very nicely (though that ability is not used in the specific implementation regarding votes in Congress). Imagine for a moment that, for whatever silly reason, Michigan and Ohio are granted one additional vote to share between them; i.e. an additional representative representing both states. This new, extra vote is effectively a set overlap, and this new vote could be represented on the map, as a lone yellow box (the same size as all the other boxes, but a different color) that is contained within the boundaries of both Michigan and Ohio.

    Anyway... there it is. That's the perceived problem and potential solution presented as clearly as I can think to present them.

    Input or ideas, anyone?

    Cheers,

    Jay

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Create a fully proportional chart of 3 sets and their areas of overlap (2007)


  8. #8
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Create a fully proportional chart of 3 sets and their areas of overlap (2007)

    SHG,

    Yes, that's it!! Thanks for the great articles. Now I just have to create one with overlapping sets --- thanks for the pointers.

    Cheers,

    Jay

  9. #9
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Create a fully proportional chart of 3 sets and their areas of overlap (2007)

    More progress!

    It turns out that the mathematical tool that I was looking for is called... Venn Polyominos!

    See: http://home.flash.net/~markthom/html...olyominos.html

    At the name "Venn "suggests, Venn polyominos support overlapping sets, and yet each polyomino is proportional to the number of members that it contains (in math lingo, each is "equal density").

    A more common, sometimes-more-specific implementation of the same concept is something called a "tile chart". It is sometimes more specific if it adds the requirement that each polyonimo have the same shape as it's constituent tiles (like here: http://bit.ly/bjjzR5 ).

    [One major problem is that there is another kind of "tile chart" that has a completely different appearance and purpose.]

    Now, what do you suppose is the simplest, fastest way create a series of Venn Polyomino (or Tile) charts representing the areas and intersections of 3 sets?

    Hmmmm....

    Cheers,

    Jay

    PS: It turns out that the term "cartogram" is very specifically linked to maps, and that is a slightly more precise and applied case of a problem that I conceive as more generalized.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Create a fully proportional chart of 3 sets and their areas of overlap (2007)

    You could use a pair of nested triangles; see attached.

    The three interior vertices have, in total, six coordinates. Seems to me you could solve simultaneous equations to make each of the seven areas proportional to membership.
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1