Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    New graph instead of a Venn (office 2002, 2003)

    1623871
    1368829
    1395171

    I have these 3 pieces of data. They were represented as a venn diagram, 2 circles (left circle= 1623871, right circle=1395171) with the intersection equal to 1368829.

    How else could i represent this data? Would a stacked bar graph be appropriate? thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    A Venn diagram is especially suitable for showing overlap between data. A stacked bar or column chart could be used to show the same data, but they don't convey the idea of overlap, so you'd have to make that clear in a caption.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New graph instead of a Venn (office 2002, 2003)

    yes, it is a good way but there is no automated way to make the 2 circles propotionate and data labels to appear in the center of the 2 circles in addition to the "overlapped" section. I have to do 30 of them a month and want to automatically make them. Is the stacked bar the best 2nd choice? thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    I'd ask the person or persons who have to use the charts.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    See <post:=265,155>post 265,155</post:> which you started 2.5 years ago and the responses. My reply in <post:=265,349>post 265,349</post:> shows how to create a proportianl Venn diagram that could be adapted realtively easily if you only have 2 circles and an overlap.

    The difiiculty is 3 circles and overlapping areas are not always possible exactly and compromises in the areas must be done.

    Steve

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New graph instead of a Venn (office 2002, 2003)

    I did read the old posts but completely missed your sample. I have 30 venns with only 2 circles. I tried to modify for 2 cicles but frankly got lost. Can you help me? thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    Here is a modified version of Steve's example. If you change the values in B2:B4, you may have to fiddle with the scale (min/max) of the chart axes.
    If the solver doesn't find a solution, enter a different guess in E23.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    I see Hans modified my original one based on 3 circles for only 2.

    This is an improved version. WIth only 2 circles the area of each is exact and solver is not needed to "massage" the areas and compromise. This version will have proportianate areas for the left and right and the put them the correct distance apart to have the overlap area match the area. (The areas are "filled" by adding hundreds of vertical lines to each area...)

    The calculations are intensive since there are many cases. It is much easier to do a stacked bar or stacked column (which I have also included for comparison). These chats only require 3 values which can be directly determined from your values...

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    Great! I was too lazy (see <post:=544,543>post 544,543</post:> in Scuttlebutt) to work out the exact solution. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I agree that a stacked bar or column chart is much easier.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    I had already had much of it done when I originally played with it 2.5 years ago. The question then was for 3 circles which lead me to post the "solver solution" since there was generally no exact answer for all the areas (you can get total of each circle and the 2 circle overlap of each exact, I recall, but this setup defines the overlap of all 3 circles so it is not variable).

    I cleaned up my working workbook, but I am not even sure if I need all the calcs, it was so long since I had created it and worked thru all the variants, I am not sure what all the calcs do anymore <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    Steve

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New graph instead of a Venn (office 2002, 2003)

    oh my goodness. This is TOOOO wonderful.

    Is there a way it can be changed to keep them perfect circles? When i use

    5,006,000
    18,727,000
    6,905,000

    the circles come out round. When i use
    8,749,000
    5,544,000
    3,162,000

    they are oblong.

    I've looked everywhere for a solution like this. I can't believe all the calculations. I would love to do circles but may switched to stacked bars to keep it simple since i have to do 30 of them. Thank you so much.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    The problem is the XY axis scales are notlined up correctly. You just need to expand the X-direction of the chart and possibly decrease the Y.

    It might be possible with a formula to add points (that are not visible) to ensure that the axes expand as needed automatically to cover the same range, though this may not be perfect. I think it would require a macro to ensure that the XY axes are manually set make perfect grid squares.

    Actually if you are going to use a macro, I think I would just use the calculations and have the macro draw the 2 overlapping circles (using the drawing tools) and have them partially transparent to show the overlap. I think this would be easier than trying to keep the chart "square". The calculations for the center of the circles and the their radii is on the calc sheet. It would just require drawing and placing them on a sheet (and scaling them to fit in a particular size, and maybe center them).

    It should not be too difficult. I don't know if I will have time to look at it tonight, but if no one takes on the challenge I will try to sit down and work on it...

    Steve

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New graph instead of a Venn (office 2002, 2003)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    You are too kind!

  14. #14
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New graph instead of a Venn (office 2002, 2003

    Steve,

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Fantastic</span hi> code!

    But the formula for Total B should read ="Total B = "&TEXT(B2+B3,"#,##0"), not ="Total B = "&TEXT(B3+B3,"#,##0").

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: New graph instead of a Venn (office 2002, 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 09-Jan-06 09:28. Added Corrected file. Fixed typo found by Sundog. Thank you.)</P>I finally had a chance to play with this.

    I got rid of the calculation sheet and do everything with user-defined functions/subroutiones. These routines could be used independently of the procedure if desired.
    The sheet has 3 data entry cells (B1:B3). D15 has the info for the graphic labels.

    The drawn object has a background (rectangle named "OverallArea") with 5 textboxes (containing the info from D15). It also contains a smaller rectangle name "VennArea" which the circles will fill up (it has no border so it is not seen). These are essentially "unchanged" by any of the code. The 2 circles ("AreaA" and "AreaB") are deleted and recreated when B1:B3 is changed.

    The routines are:
    Worksheet_Change is in the sheet code. When a new value is entered into B1,B2, or B3 this runs and calls the sub "DrawVenn" to draw the venn Diagram

    DrawVenn takes the Area of A (without [img]/forums/images/smilies/cool.gif[/img], the overlap of A and B, and the area of B (without A). It also needs the names of the 2 areas ("AreaA" and "AreaB") and the worksheet object where it will draw and from that draws 2 circles. You can change the colors of the circles here (I used vbBlue and vbRed, near the end of the code. In this procedure the size of the rectangle "VennArea" is determined and objects are scaled to fill the area. The procedure calls the function "GetDistance" and "DrawCircle"

    GetDistance takes as arguments the Area of A (without [img]/forums/images/smilies/cool.gif[/img],the area of B (without A) and the overlap of A and B and calculates the distance the 2 circles need to be apart to have the given overlap. It interpolates the area from 5 different distances. It calls the function "OverlapArea".

    OverlapArea determines the overlap of 2 circles given the 2 radii and the distance between them

    DrawCircle, given the center of the circle (X,Y coords), the radius, color, name and worksheet, will delete a previous one, then redraw at the proper size.

    I hope this helps. If you need further assistance let us know.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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