Results 1 to 15 of 18
Thread: Venn Diagram help (Excel xp)

20030616, 16:33 #1
 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

20030616, 17:57 #2
 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 688745 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

20030616, 18:20 #3
 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.

20030616, 18:29 #4
 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

20030616, 18:38 #5
 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.

20030616, 20:14 #6
 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 XYscatter. 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 Xaxis 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 "fillin". 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

20030616, 21:29 #7
 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.

20030616, 21:34 #8
 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.

20030616, 22:42 #9
 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.

20030616, 23:47 #10
 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

20030617, 03:11 #11
 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

20030617, 03:12 #12
 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.

20030617, 10:23 #13
 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) = (8053) = 27
Is A&C (w/o ABC) = 138 or is A&C = 138 so A&C(NOT [img]/forums/images/smilies/cool.gif[/img] = (13853) = 85
Is B&C (w/o ABC) = 87 or is A&C = 87 so A&C(NOT [img]/forums/images/smilies/cool.gif[/img] = (8753) = 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: 419273453=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: 171278553= 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: 246853453= 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

20030617, 14:36 #14
 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.

20030617, 15:33 #15
 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