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 $A - B, B- A, A \cap B$ and $(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 $r_1, r_2$ and their areas $A_1, A_2$, obviously

$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 $AB = d, \angle CAB = \theta, \angle CBA = \phi$. (Angles in radians.) Then, using the Cosine Rule on triangle ABC:

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

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

The intersection area is $\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, $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 $i$ from $|r_1-r_2|$ to $r_1 + r_2$. You'll have to make sure that if $r_1 = r_2$ you avoid a division by zero error. Calculate the areas for each value of $i$, and when the intersection area is closest to the one required, set $d = i$.

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

I hope you can get it to work OK.