1. Venn diagram tool

Hi,
First off, apologies if this is the wrong forum or even the wrong site...
This is a geometry question - but I haven't a clue..

What I'm trying to do is use MS Excel (2003) visual basic to display a 2 circle Venn diagram based on certain facts.

Facts are:
Population 1 (Area of circle 1)
Population 2 (Area of circle 2)
Intersect (n of Population 2 also in Population 1)

I'm struggling with calculating the distance between the center of each circle based on the Intersect.

Eg.
Pop 1 = 100
Pop 2 = 200
Intersect = 50

Many thanks for any assistance

2. Venn diagram

Hello justinbentley1
Originally Posted by justinbentley1
Hi,
First off, apologies if this is the wrong forum or even the wrong site...
This is a geometry question - but I haven't a clue..

What I'm trying to do is use MS Excel (2003) visual basic to display a 2 circle Venn diagram based on certain facts.

Facts are:
Population 1 (Area of circle 1)
Population 2 (Area of circle 2)
Intersect (n of Population 2 also in Population 1)

I'm struggling with calculating the distance between the center of each circle based on the Intersect.

Eg.
Pop 1 = 100
Pop 2 = 200
Intersect = 50

Many thanks for any assistance
You don't say why you need to draw this diagram using VBA in Excel. I presume that you want to make it interactive in some way, rather than simply using the drawing tools to create a fixed diagram on the spreadsheet.

But my question is: what has the number of elements in the intersection of the two sets to do with the distance between the centres of the circles? The answer really is: nothing at all. The circles (and they don't actually need to be circles; ellipses will do just as well) can be fixed in size and position, and any number of elements can be written in each of the four regions in the diagram - the regions denoted in set notation by $\displaystyle A - B, B- A, A \cap B$ and $\displaystyle (A \cup B)'$

Am I missing something here, or is that all OK?

3. Hi,

I'm trying to make the size of the circles representative of the population sizes.
Working from 3 variables - Population 1 (Circle 1), Population 2 (Circle 2) and the intercept.

I'm using VBA to draw the cicles, add labels, %'ages etc. Also allows me to plug in new variables and redraw the diagram.

The diagrams will be proportionally correct, which I need for presentations.

Thanks again

4. Hello justinbentley1

I assume that you want the area of each part of the diagram to be proportional to the numbers it represents. So if you call the radii of the two circles $\displaystyle r_1, r_2$ and their areas $\displaystyle A_1, A_2$, obviously

$\displaystyle A_1 = \pi r_1^2, A_2 = \pi r_2^2$

Suppose the centres of the circles are A, B and the circles meet at points C, D; let $\displaystyle AB = d, \angle CAB = \theta, \angle CBA = \phi$. (Angles in radians.) Then, using the Cosine Rule on triangle ABC:

$\displaystyle \cos \theta = \frac{d^2 + r_1^2 - r_2^2}{2r_1d}$

$\displaystyle \cos \phi = \frac{d^2 + r_2^2 - r_1^2}{2r_2d}$

The intersection area is $\displaystyle \frac{A_1\theta+A_2\phi}{\pi}-\tfrac12r_1^2\sin 2\theta - \tfrac12r_2^2\sin 2\phi$

You won't be able to find an explicit formula for the distance, $\displaystyle d$, between the centres - the best I can suggest is that you use an iterative method to find the closest value. Since you'll presumably be working in integers, you could set up a loop with values of $\displaystyle i$ from $\displaystyle |r_1-r_2|$ to $\displaystyle r_1 + r_2$. You'll have to make sure that if $\displaystyle r_1 = r_2$ you avoid a division by zero error. Calculate the areas for each value of $\displaystyle i$, and when the intersection area is closest to the one required, set $\displaystyle d = i$.

I attach an Excel 2000 file with some code that works reasonably well. I have assumed that $\displaystyle r_1 \ge r_2$.

I hope you can get it to work OK.