Circle charts - Distance between centers

[rnstore]

I have two populations, with some overlap. Population A is 100. Population B
is 70. Overlap (belong to both populations) is 10.

I want to model this in a Circle chart, with the size of circles
representing the size of populations, with overlap scaled proportionally. I
will know the population sizes and the overlap, but how can I get the
circles placed so that the data is represented proportionally?

Please respond by email also, as I don't have frequent opportunity to visit
the board.
thanks, rnstore.

[EarlK]

rnstore;

A macro could make two overlapping circles. I don't know the math for the
overlapping area of two circles, or I'd give it a go.

ActiveSheet.Shapes.AddShape(msoShapeOval, 100, 100, 50, 50).Select

--
Regards from Virginia Beach,

EarlK
ea...@livenet.net
-------------------------------------------------------------

"rnstore" <rns...@ipa.net> wrote in message
news:gVgZ4.35$%t5....@nntp1.onemain.com...

[Dave Peterson]

if you wanted the circles concentric (same center), and the area of the circles
represent the population, then:

Area of big circle A = pi * R*R
area of little circle a = pi * r*r

Solving for R and r, we see that

R= sqrt(A/pi)
r= sqrt(a/pi)

Using EarlK's stuff:

Sub test()

factor = 500 'make it big enough to see
pop_big = 100 * factor
pop_small = 70 * factor

center_x = 200
center_y = 200

r_small = ((pop_small / Application.Pi) ^ 0.5)
r_big = ((pop_big / Application.Pi) ^ 0.5)

big_x = center_x - r_big
big_y = center_y - r_big

small_x = center_x - r_small
small_y = center_y - r_small

ActiveSheet.Shapes.AddShape(msoShapeOval, big_x, big_y, r_big * 2, r_big *
2).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 43

ActiveSheet.Shapes.AddShape(msoShapeOval, small_x, small_y, r_small * 2,
r_small * 2).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 27

End Sub

=-=-
fyi,

I used factor to make the picture big enough to see.

big_x, big_y, small_x, small_y are the top left coordinates of the big and small
circles.

The center_x, center_y are the shared centers.

To find the top left corner, we just subtract the radius from the center
coordinates.

The .addshape(shape, leftpos_or_xpos, toppos_or_ypos, height_or_radius*2,
width_or_radius*2)

You can change the center_x, center_y to where you want it centered and adjust
the factor to make it the "right" size.

Also, you can change the pop_small, pop_big to see what the pictures look like
when the ratio is different than 100:70.

good luck,

--

Dave Peterson
pete...@freewwweb.com

[EarlK]

Dave;

I assumed that in the original poster's problem, one population was not
completely contained in the other. He/She says "with some overlap." So the
centers of the circles are not at the same place, and there is overlap of
the areas.

--
Regards from Virginia Beach,

EarlK
ea...@livenet.net
-------------------------------------------------------------

"Dave Peterson" <pete...@freewwweb.com> wrote in message
news:39394FF8...@freewwweb.com...

[Dave Peterson]

Got out my old CRC Math Tables (& formula book). I was going to try to solve it
in a generic form, but that turns out to be beyond me.

The formula for the segment of a circle (area defined by the chord to the
circumference):

area of segment = R^2*(acos((R-h)/h) - (R-h)*sqrt((2Rh)-h^2)

where h is the smallest distance from the center of the chord to the
circumference (remainder of the radius).

radius R = d+h (d=length center to chord)

more formulae:

d=sqrt(4*(r^2)-(c^2))/2
c=2*sqrt(r^2-d^2)

I think that you want to keep c (length of chord) the same. The area of
bigCircle_segment + area of smallCircle_segment = 10

My bet would be trial & error/solver/goal seek.

I screwed up and tried to keep h the same for both circles and got this:

Sub test()

pop_big = 100
pop_small = 70

center_x = 200
center_y = 200

r_small = ((pop_small / Application.Pi) ^ 0.5)
r_big = ((pop_big / Application.Pi) ^ 0.5)

big_x = center_x - r_big
big_y = center_y - r_big

small_x = center_x + r_small - 1.17 '1.17 take from goal seek

small_y = center_y - r_small

ActiveSheet.Shapes.AddShape(msoShapeOval, big_x, big_y, r_big * 2, r_big *
2).Select

ActiveSheet.Shapes.AddShape(msoShapeOval, small_x, small_y, r_small * 2,
r_small * 2).Select

End Sub

If you group the two circles, and then use shift+drag corner, it'll expand
nicely. Then double click and format with no fill and it looks ok.

With the height = 1.17 (area 100&70), I got the area's in the segments=

5.48847666 for big circle
4.987403342 for small circle

9.974806683 total (very close to 10!)

(In my old age, that was close enough for me!)

Good luck EarlK if you're going to make it right.

--

Dave Peterson
pete...@freewwweb.com

[Dave Peterson]

[Dave Peterson]

One more (last?) time:

Rewriting the area formula so that it depends on radius and chord length
(algebra, don't fail me now!)

area of segment =

r^2*(acos( (sqrt(4*r^2-c^2) /2*r ))) - c/4*(sqrt((4*r^2)-c^2))

and

d=sqrt(4*(r^2)-(c^2))/2

where d was center to chord distance

h = r-d (height of seqment)

This time I got (using goal seek)

chord length:
6.434458661

4.404057083 (area of big circle segment)
5.596273118 (area of small circle segment)

10.0003302 (sum of both areas--very close to 10!)

d=.5*(sqrt(4*r^2-c^2)
4.634697838 d(big circle)
3.454146417 d(small circle)

h
1.007197997 h(big circle)
1.266202303 h(small circle)

(remember that the distance between centers is

big_radius + little_radius
- height_big_circle_segment - height_small_circle_segment

Sub test()

pop_big = 100
pop_small = 70

center_x = 200
center_y = 200

r_small = ((pop_small / Application.Pi) ^ 0.5)
r_big = ((pop_big / Application.Pi) ^ 0.5)

big_x = center_x - r_big
big_y = center_y - r_big

small_x = center_x + r_small - 1.00719799708364 - 1.266202303 ' take from

goal seek
small_y = center_y - r_small

ActiveSheet.Shapes.AddShape(msoShapeOval, big_x, big_y, r_big * 2, r_big *
2).Select
ActiveSheet.Shapes.AddShape(msoShapeOval, small_x, small_y, r_small * 2,
r_small * 2).Select

End Sub

If you group the two circles, and then use shift+drag corner, it'll expand
nicely. Then double click and format with no fill and it looks ok.

--

Dave Peterson
pete...@freewwweb.com

[rnstore]

Wow, this turns out to be pretty deep stuff. Although I like algebra pretty
good, I never was any good at geometry (or trig). I understand VBA ok, so
I'll see what I can do with this.

No doubt, it will take me some time to digest this.

Thank you very much for spending your time to help me with this question.
rnstore.

"Dave Peterson" <pete...@freewwweb.com> wrote in message

news:393ABA3E...@freewwweb.com...

[Tushar Mehta]

I saved Dave Peterson's message since the original poster (rnstore)
posed an intriguing problem. Here's a solution that provides 5
equations in 5 unknowns. Unfortunately, the equations are non-linear
and consequently, not trivial to solve. However, I believe the solution
will be unique when solved numerically.

If anyone wants I will post a solution with graphical explanations
somewhere within my web site. This is one case where a picture might
indeed be worth a thousand words (or more!). However, given the amount
of work it will involve, I will do so only if there is a demand for it.

First, I believe the formula attributed to CRC Math Tables is incorrect
as posted. Most likely, a typo while posting it. The formula for a
circle segment, from first principles, is
R^2*(acos((R-h)/R) - (R-h)*sqrt((2Rh)-h^2)

Using Dave's terminology and extending it to 2 circles:

We know the area of circle 1. Let it be A1.
Similarly, let the known area of circle 2 be A2.

Let the radius of the first circle be R1.
Let the radius of the 2nd circle be R2.

This yields the first 2 equations:
(1) A1 = Pi()*R1^2
(2) A2 = Pi()*R2^2.

Let the distance between the two centers be L.

Given that the two circles intersect (and there will be two and only two
points at which they intersect):

Let the chord that connects the two intersection points have length c.

Draw a line connecting the centers of the two circles. This will go
through the center of the chord.

Let the smallest distance from the center of the chord to the
circumference of the first circle be h1. [This is the remainder of a
radius that intersects the chord at right angles]. Consequently, the
smallest distance between the center of circle 1 and the chord is R1-h1.

Use similar definitions for circle two to get h2 and R2-h2.

This yields the third equation:

(3) L = (R1 - h1) + (R2 - h2)

As shown by Dave, the area framed be the two intersecting segments is
the sum of the segments for circle 1 and circle 2. Let this be AS, and
since this is a known quantity, it yields the fourth equation:

(4) AS = R1^2*(acos((R1-h1)/R1) - (R1-h1)*sqrt((2R1h1)-h1^2)
+R2^2*(acos((R2-h2)/R2) - (R2-h2)*sqrt((2R2h2)-h2^2)

To get the fifth equation, draw the radius from the center of circle 1
to one of the points at which the two circles intersect. Draw the
radius for the 2nd circle to the same intersection point. Together with
1/2 the chord and the straight lines connecting the centers, this
defines two right triangles. Since the two triangles share the chord
part, Pythogaros' (spelling?) Theorem yields the fifth equation:

(5) R1^2 - (R1 - h1)^2 = R2^2 - (R2 -h2)^2.

This yields a unique value for L as follows:

Use (1) and (2) to solve for R1 and R2.

Use those results and (5) to solve for h1 as a function of h2.

Plug that algebraic formula of h1 into (4) to solve for h2.

Go back and use (5) to solve h1.

Use the results of R1, R2, h1, and h2 and (3) to get L.

OK, back to the game (that's basketball Game 2 Lakers vs. Pacers),
folks.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
--
In <393A96DD...@freewwweb.com>, Dave Peterson
<pete...@freewwweb.com> wrote

[Dave Peterson]

very nice and yes, it was a posting error (thank goodness)

and

Pythagoras (vbg).

--

Dave Peterson
pete...@freewwweb.com