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

    Venn Diagram help (Excel xp)

    I have to make a Venn Diagram.

    A: n=419 (27%)
    B: n=171 (11%)
    C: n=246 (16%)

    these are the 3 circles. A intersects B n=80, 19%. B intersects C n=87, 51% and A intersects C n=138, 33%. Is this enough data to produce the venn diagram with 3 circles. Also, how do i do it in excel? thank you for the help

  2. #2
    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: Venn Diagram help (Excel xp)

    Your percentages do not add to 100, what is the REST of the items?
    A+B+C = 836
    To get the percentages means the total must be somewhere between 1524 and 1581. You have between 688-745 pieces of data unaccounted for. Are these in a separate circle D that do NOT overlap at all?

    What are the number of points that intersect A and B and C? You do NOT list it, is it zero?

    Steve

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

    Re: Venn Diagram help (Excel xp)

    ok, sorry. There are 836 people. Of that, A has 419 people, B has 171, and C has 246 people. Disregard the previous %'s that i used. There are 80 people that are in both A and B. 87 people are in both B and C. and 138 people are in both A and C.

    Do i need anything else? Do i have to have how many people are in A, B and C? thank you for the help.

  4. #4
    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: Venn Diagram help (Excel xp)

    How many are in all three (A and B and C)? Is is zero as you imply?

    Steve

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

    Re: Venn Diagram help (Excel xp)

    I don't know. They will have to give me the number. I wasn't sure if i needed that piece of data. Can you tell me how to get started if say the number for all 3 was 25? then i could change it and use your formulas etc when i get the right number. I just don't know how to take the numbers and make them produce the venn. thank you for the help.

  6. #6
    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: Venn Diagram help (Excel xp)

    If you have XL XP I think it does Venn directly.

    I was thinking of using an XY-scatter. I was trying to figure out the equations to draw all of them given information on the overlap.
    I have not had the time to do it.
    1) radius of circle x= sqrt(Nx/pi())
    2) define the center of the CircleA (eg 0,0). WIth center and radius you can draw it.
    3) Assume circle B has a center on X-axis also. Y value is calculated by the distance required to make the overlap area = given overlap of AB (a little geometry problem, using arcs and triangles and pie sections.
    4) Now th real tricky part. You need to calculate the center of Circle C (both X, Y) so that the areas of AC and BC match the given.
    5) you MIGHT not need the overlap of all 3 and calculating this might be interesting (it could come out after you place Center C to match the overlaps, but I am not sure.

    You could do it also with a bubble chart, but you would still need the coords of the centers. though you could just "eyeball it" to get the overlap approximately.

    Then again, if you are going to eyeball, you might want to do it in powerpoint or another drawing program to fill in all the areas with different colors. The XY is a bear to "fill-in". I usually just draw (w/XY coords) the horiz or vert lines or maybe some diagonals if I get energetic using different colors.

    The bubble will fill, but the overlaps can not change color. You are limited since a bubble chart can not be combined, where a true XY you can draw almost anything with coords.


    Hope this helps,
    Steve

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

    Re: Venn Diagram help (Excel xp)

    Thank you for the help. Does anyone know of software that will create a Venn Diagram? I've seen some on the Internet that creates circles with labels but it doesn't accept data. i need to input the numbers for 3 circles and their intersections and have it come out to graphically represent those numbers.

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

    Re: Venn Diagram help (Excel xp)

    If the total number of people is 836, of which 419 in A, 171 in B and 246 in C, there is no overlap at all, since 419 + 171 + 246 is exactly equal to 836, so there is nothing left. So there must be something wrong in the numbers you give us.

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

    Re: Venn Diagram help (Excel xp)

    hmmm, this is what i'm thinking in the attached excel. The problem is that i need the circles and the overlap sections to represent proportionately the numbers. So the A circle should be more than 200% larger than the B circle and not quite twice as big as the C circle since it is 419 and B is 171 and C is 246.

  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: Venn Diagram help (Excel xp)

    Based on your picture, I would say:
    ONLY A = 419
    ONLY B = 246
    ONLY C= 171

    A+B but NOTC = 80
    A+B but NOT C = 138
    B+C but NOT A=87

    A+B+C = 53

    Total A = 419+80 +138+53 = 690
    Total B =171+80 + 87+53 =391
    Total C = 246+138+87+53 = 524

    Total number = 1194

    These will make a difference in the calcs as the circles should be based on the TOTAL of A, B, and C
    Steve

  11. #11
    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: Venn Diagram help (Excel xp)

    Here is file with how I imagine it.
    Circle A is centered at 0,0 but can be changed
    Circle B and circle C, adjust the coord to put them about where you want.

    The program calculates the area of the overlaps (it uses Heron's formula to caluculat the are of the triangle and then uses that are to get info to calculate the 2 "pie Areas) the Sum of the 2 pies - the 2 triangles = sum of the overlap.

    After you place the circles, run the solver to minimize Cell M11 (the calc area, vs the desired area) and it will adjust the center for C and the Y value for Circle B

    Based on the circles and the radii, the circles are drawn (XY scatter), you could add cells with labels if desired. I also added vertical lines to "Fill". You can adjust the number of lines though if you want more than 14 you will have to extend the range and copy the formula pairs. You can offset the start of any of the lines in case the line overlap too much.

    Hope this helps, it was an interesting exercise.
    Steve

  12. #12
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Venn Diagram help (Excel xp)

    As I see his diagram, the numbers are correct and add correctly. I don't understand where you get

    Total A = 419+80 +138+53 = 690
    Total B =171+80 + 87+53 =391
    Total C = 246+138+87+53 = 524

    Total number = 1194

    But, I am no math person so I may be wrong. Anyway, I also found that if you turn off the auto format, you can resize and reposition the circles to get the effect you want (see attached). I don't know if this can be done automatically based on some data series.

  13. #13
    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: Venn Diagram help (Excel xp)

    I was not sure how to read his chart.
    A&B&C = 53 is clear
    Is A&B (w/o ABC) = 80 or is A&B = 80 so A&B(NOT C) = (80-53) = 27
    Is A&C (w/o ABC) = 138 or is A&C = 138 so A&C(NOT [img]/forums/images/smilies/cool.gif[/img] = (138-53) = 85
    Is B&C (w/o ABC) = 87 or is A&C = 87 so A&C(NOT [img]/forums/images/smilies/cool.gif[/img] = (87-53) = 34

    I assumed the JUST A (Not AB, Not AC, Not ABC) = 419 though it could be the TOTAL in A is 419 and the JUST A is Then: 419-27-34-53=254
    I assumed the JUST B (Not AB, Not BC, Not ABC) = 171 though it could be the TOTAL in B is 171 and the JUST B is Then: 171-27-85-53= 57
    I assumed the JUST C (Not AC, Not BC, Not ABC) = 246 though it could be the TOTAL in C is 246 and the JUST C is Then: 246-85-34-53= 74

    In that case the TOTAL number is 584.

    I wasn't sure how to interpret his numbers. I assumed that the numbers were unique to the "section" and NOT combinations, though an alternate interpretation is valide. If you put either set in, my spreadsheet can calculate. It can even be modified to enter in the other series differently.

    Steve

  14. #14
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Venn Diagram help (Excel xp)

    Okay, I see your point. That brings up another question, though. What is the convention for describing these types of data? Interesting discussion on several levels.

  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: Venn Diagram help (Excel xp)

    I am not sure what all the conventions are. I haven't used them in a while. Usually you see it with smaller sets and the "sections" actually contain the entire subset of data that goes in that "section"

    I alway understood it to be the way I initially described. The count of items in a region ONLY belong to the region.
    The "triangle overlap" of ABC (53) are the number of items that are in A and B and C
    The overlap of A and B but without the overlap of C is 80 (so the whole overlap of AB "oval" is 80 +53 = 133
    The section of A that overlaps NOTHING is 419, so the enitre set of A = 419 + 80 + 138 + 53 = 690

    Overall the total should be the sum of these:
    ONLY A = 419
    ONLY B = 246
    ONLY C= 171

    A+B but NOTC = 80
    A+B but NOT C = 138
    B+C but NOT A=87

    A+B+C = 53

    Total number = 1194

    I do NOT know what convention "JHA900" used, but as I pointed out, my spreadsheet can do it either way, you just have to calculate it differently. It is only an example spreadsheet, it is not meant to be anytime of addin. I did it as a theoretical exercise: I have no use for it.
    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
  •