How I Made a Venn Diagram Plotter in Excel

Before I reveal how to create a Venn diagram plotter using basic trigonometry and Excel, I would like to thank my co-work Shea for challenging me to solve this problem.

What is the problem?

Given the areas of two circles and how much they overlap, plot a Venn diagram showing their relation in space.

What must you know first?

In order to understand my solution, you need to have some math background in trigonometry, as well as some macro-writing experience in Excel.

How to solve it?

We can easily draw circles in Excel, re-size them, and define their coordinates. Finding out the diameters of the circles given their areas is easy: A=πr². The y-coordinates of the two circles are essentially the same. The x-coordinates of the two circles can be determined if the distance between their loci is known.

I define d to be this distance, h to be the length of their common chord, and everything else as shown in the diagram below. Imagine the two circles are just starting to touch each other.

d = h/2 * cos(arcsin(h/2/r1)) + h/2 * cos(arcsin(h/2/r2))

r1 and r2 can be calculated, but h must be further determined.

Now, what if we test different values of h until we find a value for the overlap area that is equal or almost equal to the given value. We can rely on the following equation to calculate it.

Area of the overlap = Area of the left sector + Area of the right sector - Area of the quadrilateral

The expression on the right can also be expressed using h, r1, and r2:

πr1² (θ1/360) + πr2² (θ2/360) - r1² sin(θ1)/2 - r2² sin(θ2)/2

θ1 and θ2 can be expressed by h:

θ1 = 2 arcsin( h/2/r1 )
θ2 = 2 arcsin( h/2/r2 )

The above formula will work as long as the loci of the two circles are on different sides of the common chord.

Image that the two circles move closer together and their loci fall on the same side of the common chord. Of course, we assume that r1 is greater or equal to r2 in order for the math to make sense.

In this case, d = r1 cos(arcsin(h/2/r1)) - r2 cos(arcsin(h/2/r2))

We can test different h to get a value for the overlapping area until this value equals to or approximately equals to the given value for the overlapping area.

Area of the overlap = Area of Sector (orange outline) + Area of the triangle in the smaller circle (solid green) + Area of Segment (solid red)

Again, in mathematical symbols, the area of the overlap is:

πr2² (θ2/360) + r2² sin(θ2)/2 + πr1² (θ1/360) - r1² sin(θ1)/2

Similarly, θ1 and θ2 can be expressed in h:

θ1 = 2 arcsin(h/2/r1)
θ2 = 2 arcsin(h/2/r2)

With these equations, we are ready to write a macro to help us find d.

Basically, we test h from 0 to 2(r2), assuming that r1≥r2, to see if the overlap area formula in Case 1 yield any value that's equal to our target overlap area. Picture the two circles moving closer together and the common chord increases in size.

If the macro does not find a match, we keep testing h, but from 2(r2) to 0. Picture the two circles moving even closer together, and their common chord decreases in size. Obviously, when h=0, the entire smaller circle will reside in the larger circle.

When I wrote my macro, I increased my h by 0.001 in a DO...UNTIL loop. If you math is very good, you will soon realize that as h gets close in range to 2(r2), each time h is adjusted, you see a greater change in the value of the overlap area than before. Of course, this can be proven mathematically, but it can also be pictured even if you don't want to get into the math.

Here are some output pictures of my Venn Diagram macro (when one circle completes eats the other one, I define both circles to be purple):