Venn Diagrams (v2)

barry(uk)

New Member
Joined
Oct 14, 2002
Messages
9
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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)

New Member
Joined
Oct 14, 2002
Messages
9
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
Joined
Oct 14, 2002
Messages
9

ADVERTISEMENT

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
Joined
Feb 17, 2002
Messages
4,239
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
Joined
Oct 14, 2002
Messages
9

ADVERTISEMENT

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
Joined
Feb 17, 2002
Messages
4,239
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
Joined
Feb 17, 2002
Messages
4,239
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.
 

Forum statistics

Threads
1,144,150
Messages
5,722,796
Members
422,459
Latest member
Chriselff

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Top