Venn Diagrams (v2)

[barry(uk)]

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?

 

[Damon Ostrander]

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

 

[barry(uk)]

[No message]

 

[barry(uk)]

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)]

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]

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)]

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]

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

 

[barry(uk)]

Appreciate your help.

 

[Damon Ostrander]

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.