Venn Diagrams (v2)

barry(uk)

New Member
I'll rephrase this:
How do I get the circles and their overlaps in a venn diagram to be representive of data in a range?

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Damon Ostrander

MrExcel MVP
Hi Barry,

How do you intend to make the diagram? Are you using a Bubble chart? Or are you drawing the circles as Shape objects?

Damon

[No message]

barry(uk)

New Member
Hi Damon - I'm not sure. I've got four circles to overlap. I created a venn diagram but this only represents 13 overlaps. The only other way would be to use shapes. Have you done something similar before? Please advise! Barry

barry(uk)

New Member
Hi Damon - Ignore last reply - use this one. I've got four circles which represent all 15 possible overlaps. I created a venn diagram but this only represents 13 overlaps. The only other way would be to use shapes. Have you done something similar before? Please advise! Barry

Damon Ostrander

MrExcel MVP
Hi Barry,

I'm not sure how you get 15 overlaps. With four circles it seems to me that you only have 11 combinations of overlaps: 1-2, 1-3, 1-4, 2-3, 2-4, 3-4, 1-2-3, 2-3-4, 1-3-4, 1-2-4, 1-2-3-4. Am I missing something? Or are you including the individual circles as if they were combinations, which would give 15 "combinations".

Also, do your data values correspond to the areas that you want in the various combinations? If so I believe you could use Solver to solve all the conditions simultaneously to find the positions of the four circles that would satisfy all the overlap areas, basically solving for the x,y coordinates of each circle centerpoint and the radius of each circle. Once you have these data values, drawing the circles using Shapes should be fairly straightforward. I would recommend filling them with different semi-transparent colors so that the overlapping areas show up in different colors.

Damon

barry(uk)

New Member
Damon - yes there are 11 combinations, 15 including the individual circles. And the data values do correspond to the areas. I've never used Solver before - any tips? Thanks for your help. Barry

Damon Ostrander

MrExcel MVP
Hi Barry,

This may take a while (I have to get back to work) so I'll get back to you later. This is a very interesting problem.

Damon

Damon Ostrander

MrExcel MVP
Hi Barry,

I have now had a chance to take a more detailed look at the problem. In order to define the Venn diagram I believe it is necessary to be able calculate for any 4 circles what the area is in each of the overlaps mentioned before. This turns out not to be a trivial problem, although I believe it is solvable. I do not have time to work through this right now, or to search out a solution (I would guess that someone else has solved it and published the solution). Once this solution is in hand I believe I could set up Solver to invert it, i.e., to find the circle geometry that gives the desired overlaps, and could also set up a macro to draw it.

If you have already solved the area calculation part of the problem, let me know the solution and I'll do the Solver part.

Damon

PS. This problem might be a good candidate for the Challenge of the Month on MrExcel.

Replies
3
Views
147
Replies
3
Views
329
Replies
1
Views
2K
Replies
1
Views
370
Replies
0
Views
155

1,181,106
Messages
5,928,102
Members
436,588
Latest member
mummabare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back