Venn Diagram help (Excel xp)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 = (138-53) = 85
Is B&C (w/o ABC) = 87 or is A&C = 87 so A&C(NOT = (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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

The “convention” is usually to use Set Theory notation. The union, intersect and not (compliment) operators are used to describe the various conditions. I’m unable to show them properly here but, for instance, “A and B but not C” would be represented as
A n B | C
where n is “intersect” and | is “not”.
Similarly A n B n C or A u B | C where “u” is union, represent other regions.

or the last one again (I’m a bit rusty here ) A u B n C’ where C’ is everything not in C.

Alan

Reply | Quote

I think the question was geared to the convention of the DIAGRAM itself. In post 265299 a diagram was attached, and the question is with how the numbers are written and what they mean NOT how they are written. I didn’t try to dig up the symbols so I used the AND, NOT just for myself.

I assume you use the Venn diagrams to some extent. Based on the attachment in post 265299. How would you “interpret” the numbers? My interpretation is in post 265310 and some reiteration and clarification in post 265558 , though I did discuss the an alternate in post 265447 whihc (to means makes less sense).

Any opinions? As I mentined earlier, I don’t use them alot and haven’t used them in a while.
Steve

Reply | Quote

I’m not sure what you’re saying here Steve. As far as the diagram itself goes, I think it’s conventional to place a number in each region and it is understood that it corresponds to the minimal bounded region that it appears in. As far as specifying the data unambiguously, that’s where set notation comes in. For instance:
A – 50
A n B – 27
A n C – 15
A n B n C – 5
gives all the information for the regions contained in A, in an unambiguous manner. It’s also possible to use rather more obscure notation to describe each minimal subset region of A. Using the data above, these are the four subset regions of A on the diagram:
A n B n C – 5
(A n n (A n B n C)’ – 22
(A n C) n (A n B n C)’ – 10
A n (B u C)’ – 13

As far as the interpretation of the original data given goes, I think (on a quick look) that both interpretations are valid because of the ambiguous way in which the data is described. That’s about all I can add I’m afraid.

Alan

Reply | Quote