Thread: 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

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 and

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

Grandad

Hi,
Thanks for the reply,

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

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 and their areas , obviously



Suppose the centres of the circles are A, B and the circles meet at points C, D; let . (Angles in radians.) Then, using the Cosine Rule on triangle ABC:





The intersection area is

You won't be able to find an explicit formula for the distance, , 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 from to . You'll have to make sure that if you avoid a division by zero error. Calculate the areas for each value of , and when the intersection area is closest to the one required, set .

I attach an Excel 2000 file with some code that works reasonably well. I have assumed that .

I hope you can get it to work OK.

Grandad