Calculate the Circumference of an ellipse
M
David Biddulph
http://home.att.net/~numericana/answer/ellipse.htm, but otherwise Google is
your friend.
If you are asking whether Excel has an inbuilt function for this, the answer
(I believe) is no.
--
David Biddulph
"M" <M...@discussions.microsoft.com> wrote in message
news:C11245B9-1822-421F...@microsoft.com...
Paul C
as a add in.
Depending on the accuracy desired this can get fairly involved
This reference give a fairly good sysopsis of the issue, and does have a
couple for simplier formulas that will get you close.
http://mathforum.org/dr.math/faq/formulas/faq.ellipse.circumference.html
(Sorry - I can't seem to get hyperlinking to work from my reader so jusy
copy and paste to a web browser)
--
If this helps, please remember to click yes.
JLatham
algebra the first time around) and I don't even play one on TV or in any
Intel advertisements. But...
You got me curious so I went searching for coded solutions to the problem
and couldn't find any expressed as an Excel formula. What I did discover is
that a sharp fellow by the name of Srinivasa Ramanujan came up with 2
formulas in 1914 that are now accepted as being very accurate, with the 2nd
effort being the more accurate of the two. All this is shown at
http://en.wikipedia.org/wiki/Circumference
So I worked at implementing his 2nd approximation as an Excel spreadsheet
formula and I believe I've succeeded:
Given the ellipse's major semi-axis in B1 and
the ellipse's minor semi-axis in B2, then
=(0.5*(B1+B2))*(1+((3*((B1-B2)/(B1+B2))^2)/(10+SQRT((4-(3*((B1-B2)/(B1+B2))^2))))))
gives me results shown on that same Wiki page for the various values of b
with a value of 10000 for the major semi-axis value.
Since this particular approximation appears to be the Gold Standard of all
approximations for the circumference of an ellipse, hopefully it will suit
your needs.
Dana DeLouis
function. Here, we factor out a common expression, and add in the 2 Pi
to get the Perimeter. This uses the first example from your reference:
Sub TestIt()
'// Ellipse Perimeter
Debug.Print Perimeter(10000, 9975)
End Sub
Function Perimeter(a, b)
Dim k As Double
k = 3 * ((a - b) / (a + b)) ^ 2
Perimeter = (a + b) * (1 + k / (10 + Sqr(4 - k))) * [Pi()]
End Function
Returns:
62753.3378298691
I get the same result using a math program...
a = 10000;
b = 9975.;
4*a*EllipticE[1 - b^2/a^2]
62753.3378298691`
Wow. I would say that's as good an approximation as it gets.
Thanks for the reference. :>)
Since the eccentricity is not very large, we can sense that the solution
"should" be a little less than the following, which it is.
? 2*10000*[Pi()]
62831.8530717959
= = = = =
Dana DeLouis
David Biddulph
a CIRCLE with radius 10000.
The simple approximation for the ellipse with semi-axes 10000 and 9975 is
=2*PI()*SQRT(10000*9975)
which comes to 62753.26411, which is, of course, a lot closer to your
complicated formula, but gets further away as the eccentricity increases
(but is much closer than the circumscribing circle which you had).
--
David Biddulph
"Dana DeLouis" <ddel...@bellsouth.net> wrote in message
news:eSMAtNWb...@TK2MSFTNGP04.phx.gbl...
JLatham
I can easily code up exactly what was put into the formula. The hard work
has been done in figuring out the original buildup of that formula.
I'm going to defer to David regarding the accuracy of anything or
relationship to a circle etc. In the reading I did, some models work well
for an ellipse that comes close to being a circle, while others work better
for a flatter ellipse. But it was generally accepted that the model that I
coded from is the 'most' accurate of all for both almost-a-circle and
almost-a-straight line ellipses.
Note that on the referenced Wikipedia page, 10000 and 9975 returns a value of
9987.5039111393
62753.3378298691 looks more like a value that would appear with the minor
semi-axis at a very small value (and differs by one decimal place). I'll
look at this closer shortly. And I'll post back with VBA code for a function
vs worksheet formula.
"Dana DeLouis" wrote:
> .
>
JLatham
circle with a radius of 10000 has a perimeter of 62753+, while an ellipse
with major semi-axis of 10000 and minor semi-axis of 9975 has a perimeter of
'only' 9987.5. But I'm just going by the table provided on the Wikipedia
page....
Nevertheless, here are two functions that are based on the same thing that
the worksheet formula was, and that give the same results. The only
difference in the two is that in one, I broke out some of the mid-formula
calculations and do it all in 4 lines of code, while in the 2nd one, it's all
done in 1 calculation.
Function Ram2Perimeter(majorAxis As Double, minorAxis As Double) As Double
'uses Ramanujan's second formula to approximate the
'perimeter of an ellipse
'INPUTS: majorAxis = the major semi-axis value
' minorAxis = the minor semi-axis value
'OUTPUTS: estimated circumference, or
' if major semi-axis is = minor semi-axis,
' returns circumference of circle of 2*semi-axis diameter
' -1 if error encountered
'
'Reference: http://en.wikipedia.org/wiki/Circumference
'
'worksheet equivalent formula where
' B1 holds the majorAxis value and
' B2 holds the minorAxis value
'note that this does not account for circles
' combine these into one long formula for a worksheet:
'=(0.5*($B$1+A5))*(1+((3*(($B$1-A5)/($B$1+A5))^2)/
'(10+SQRT((4-(3*(($B$1-A5)/($B$1+A5))^2))))))
'
Dim swapValue As Double
Dim majorPlusMinor As Double
Dim majorMinusMinor As Double
Dim quotentSquared As Double
On Error GoTo RamanujanCirErr
'no such thing as negative numbers here!
majorAxis = Abs(majorAxis)
minorAxis = Abs(minorAxis)
'just to be technical, if minor axis > major axis, swap
If minorAxis > majorAxis Then
swapValue = majorAxis
majorAxis = minorAxis
minorAxis = swapValue
End If
'special case: if majorAxis=minorAxis, then it's a circle
If majorAxis = minorAxis Then
Ram2Perimeter = (4 * Atn(1)) * (2 * majorAxis) ' = pi*D
Exit Function
End If
majorPlusMinor = majorAxis + minorAxis
majorMinusMinor = majorAxis - minorAxis
quotentSquared = (majorMinusMinor / majorPlusMinor) ^ 2
Ram2Perimeter = (0.5 * majorPlusMinor) * _
(1 + ((3 * quotentSquared / _
(10 + Sqr((4 - 3 * quotentSquared))))))
On Error GoTo 0
Exit Function
RamanujanCirErr:
Err.Clear
Ram2Perimeter = -1
End Function
Function Ram2Perimeter2(majorAxis As Double, minorAxis As Double) As Double
'uses Ramanujan's second formula to approximate the
'perimeter of an ellipse
'INPUTS: majorAxis = the major semi-axis value
' minorAxis = the minor semi-axis value
'OUTPUTS: estimated circumference, or
' if major semi-axis is = minor semi-axis,
' returns circumference of circle of 2*semi-axis diameter
' -1 if error encountered
'
'Reference: http://en.wikipedia.org/wiki/Circumference
'
'worksheet equivalent formula where
' B1 holds the majorAxis value and
' B2 holds the minorAxis value
'note that this does not account for circles
' combine these into one long formula for a worksheet:
'=(0.5*($B$1+A5))*(1+((3*(($B$1-A5)/($B$1+A5))^2)/
'(10+SQRT((4-(3*(($B$1-A5)/($B$1+A5))^2))))))
'
Dim swapValue As Double
On Error GoTo RamanujanCir2Err
'no such thing as negative numbers here!
majorAxis = Abs(majorAxis)
minorAxis = Abs(minorAxis)
'just to be technical, if minor axis > major axis, swap
If minorAxis > majorAxis Then
swapValue = majorAxis
majorAxis = minorAxis
minorAxis = swapValue
End If
'special case: if majorAxis=minorAxis, then it's a circle
If majorAxis = minorAxis Then
Ram2Perimeter2 = (4 * Atn(1)) * (2 * majorAxis) ' = pi*D
Exit Function
End If
Ram2Perimeter2 = (0.5 * (majorAxis + minorAxis)) * _
(1 + ((3 * (((majorAxis - minorAxis) / (majorAxis + minorAxis)) ^ 2) / _
(10 + Sqr((4 - 3 * (((majorAxis - minorAxis) / (majorAxis + minorAxis)) ^
2)))))))
On Error GoTo 0
Exit Function
RamanujanCir2Err:
Err.Clear
Ram2Perimeter2 = -1
End Function
"Dana DeLouis" wrote:
> .
>
David Biddulph
The table shows Pr, the perimetric radius. The circumference is 2*PI()*Pr.
--
David Biddulph
"JLatham" <JLa...@discussions.microsoft.com> wrote in message
news:E67FDB04-2702-4760...@microsoft.com...
Dana DeLouis
> 'only' 9987.5. But I'm just going by the table provided
> on the Wikipedia
I believe your original equation found the "Parametric Radius (Pr), and
not the Perimeter.
n = 9987.5039111393
Multiply by 2 Pi....
2*Pi*n
62753.3378298691
Which checks with an elliptical integral version of the function.
4*a*EllipticE[1 - b^2/a^2]
62753.3378298691
(A quick check of the series expansion shows that about 50-100 loops
will give pretty high precision.)
I only mentioned the circle solution just to have some kind of idea what
the solution should be close to. Kind of a quick check. However,
David's quick check is a lot closer though. I like it :>)
= = = = = = = = =
HTH
Dana DeLouis
JLatham
currently calculated and multiply it by 2 x Pi we have the actual
circumference? I'll make appropriate modifications and then we're there.
Thanks very much for clarifying my misunderstanding of what the heck is
going on. And for sticking with the discussion so that you could help me
overcome my obvious shortcomings (told you I flunked college algebra 1st time
out <g>)
"David Biddulph" wrote:
> .
>
JLatham
the functions to calculate the actual (approximated) perimeter/circumference
of the ellipse, and have left the perimetric radius as a 'way point" in the
process should anyone need that value. The previous worksheet formula was
revised in the comment in these functions to also calculate the approximated
perimeter.
Function Ram2Perimeter(majorAxis As Double, minorAxis As Double) As Double
'uses Ramanujan's second formula to approximate the
'perimeter of an ellipse
'INPUTS: majorAxis = the major semi-axis value
' minorAxis = the minor semi-axis value
'OUTPUTS: estimated circumference, or
' if major semi-axis is = minor semi-axis,
' returns circumference of circle of 2*semi-axis diameter
' -1 if error encountered
'
'Reference: http://en.wikipedia.org/wiki/Circumference
'Acknowledgment: Thanks to David Biddulph and Dana DeLouis for
' helping me realize the difference between parimetric radius and
circumference
'
'worksheet equivalent formula where
' B1 holds the majorAxis value and
' B2 holds the minorAxis value
'=(0.5*($B$1+A5))*(1+((3*(($B$1-A5)/($B$1+A5))^2)/(10+SQRT((4-(3*(($B$1-A5)/($B$1+A5))^2)))))) * (2 * Pi())
'
Dim swapValue As Double
Dim majorPlusMinor As Double
Dim majorMinusMinor As Double
Dim quotentSquared As Double
On Error GoTo RamanujanCirErr
'no such thing as negative numbers here!
majorAxis = Abs(majorAxis)
minorAxis = Abs(minorAxis)
'just to be technical, if minor axis > major axis, swap
If minorAxis > majorAxis Then
swapValue = majorAxis
majorAxis = minorAxis
minorAxis = swapValue
End If
'special case: if majorAxis=minorAxis, then it's a circle
If majorAxis = minorAxis Then
Ram2Perimeter = (4 * Atn(1)) * (2 * majorAxis) ' = pi*D
Exit Function
End If
majorPlusMinor = majorAxis + minorAxis
majorMinusMinor = majorAxis - minorAxis
quotentSquared = (majorMinusMinor / majorPlusMinor) ^ 2
'calculate the perimetric radius
Ram2Perimeter = (0.5 * majorPlusMinor) * _
(1 + ((3 * quotentSquared / _
(10 + Sqr((4 - 3 * quotentSquared))))))
'calculate the circumference [ 4 x Atn(1) = Pi ]
Ram2Perimeter = Ram2Perimeter * 2 * (4 * Atn(1))
On Error GoTo 0
Exit Function
RamanujanCirErr:
Err.Clear
Ram2Perimeter = -1
End Function
Function Ram2Perimeter2(majorAxis As Double, minorAxis As Double) As Double
'uses Ramanujan's second formula to approximate the
'perimeter of an ellipse
'INPUTS: majorAxis = the major semi-axis value
' minorAxis = the minor semi-axis value
'OUTPUTS: estimated circumference, or
' if major semi-axis is = minor semi-axis,
' returns circumference of circle of 2*semi-axis diameter
' -1 if error encountered
'
'Reference: http://en.wikipedia.org/wiki/Circumference
'Acknowledgment: Thanks to David Biddulph and Dana DeLouis for
' helping me realize the difference between parimetric radius and
circumference
'
'worksheet equivalent formula where
' B1 holds the majorAxis value and
' B2 holds the minorAxis value
'=(0.5*($B$1+A5))*(1+((3*(($B$1-A5)/($B$1+A5))^2)/(10+SQRT((4-(3*(($B$1-A5)/($B$1+A5))^2)))))) * (2 * Pi())
'
Dim swapValue As Double
On Error GoTo RamanujanCir2Err
'no such thing as negative numbers here!
majorAxis = Abs(majorAxis)
minorAxis = Abs(minorAxis)
'just to be technical, if minor axis > major axis, swap
If minorAxis > majorAxis Then
swapValue = majorAxis
majorAxis = minorAxis
minorAxis = swapValue
End If
'special case: if majorAxis=minorAxis, then it's a circle
If majorAxis = minorAxis Then
Ram2Perimeter2 = (4 * Atn(1)) * (2 * majorAxis) ' = pi*D
Exit Function
End If
'calculate the perimetric radius
Ram2Perimeter2 = (0.5 * (majorAxis + minorAxis)) * _
(1 + ((3 * (((majorAxis - minorAxis) / (majorAxis + minorAxis)) ^ 2) / _
(10 + Sqr((4 - 3 * (((majorAxis - minorAxis) / (majorAxis + minorAxis)) ^
2)))))))
'calculate the circumference [ 4 x Atn(1) = Pi ]
Ram2Perimeter2 = Ram2Perimeter2 * 2 * (4 * Atn(1))
On Error GoTo 0
Exit Function
RamanujanCir2Err:
Err.Clear
Ram2Perimeter2 = -1
End Function
Dana DeLouis
> Is there a formula to Calculate the Circumference of an ellipse in excel?
Hi. Here's something a little different that I like to do.
Here, we first use a math program to check a solution out to 29 digits.
We then convert it to excel to get the same precision.
Again, it's something most won't use. Just an educational exercise. :>)
a = 10000;
b = 9000;
h = (a - b)/(a + b);
Pi*(a + b)*Hypergeometric2F1[-1/2, -1/2, 1, h^2];
59731.604325248287448200487129
And in Excel...
? EllipsePerimeter(10000, 9000)
59731.604325248287448200487129
Function EllipsePerimeter(aa, bb)
Dim a, b
Dim k, h, h2
Dim n, d, s
Dim J As Long
a = CDec(aa)
b = CDec(bb)
n = CDec(1)
d = n
h2 = n
s = n
k = -n / 2
h = (a - b) / (a + b)
h = h * h
For J = 1 To 10
n = n * (J - 1 + k)
d = d * J
h2 = h2 * h
s = s + (n * n) / (d * d) * h2
Next J
EllipsePerimeter = CDec("3.14159265358979323846264338328") * s * (a + b)
End Function
= = = = = = = = = = = =
Dana DeLouis
Dana DeLouis
> ((majorAxis - minorAxis) / (majorAxis + minorAxis)) ^ 2
Hi. Just to mention if interested. If I am not mistaken, one does not
need to swap the variables. This is because (majorAxis - minorAxis)
gets squared. The result in the same positive value either way.
The code below is the same thing just to demonstrate.
Sub TestIt()
'// Ellipse Perimeter
Debug.Print Perimeter(10000, 9975)
Debug.Print Perimeter(9975, 10000)
End Sub
Returns:
62753.3378298691
62753.3378298691
Function Perimeter(a, b)
Dim k As Double
If a = b Then
Perimeter = 2 * [Pi()] * a
Else
k = 3 * ((a - b) / (a + b)) ^ 2
Perimeter = (a + b) * (1 + k / (10 + Sqr(4 - k))) * [Pi()]
End If
End Function
= = = = = =
HTH
Dana DeLouis
Dana DeLouis
>> If majorAxis = minorAxis Then
Oops! Me bad. You talked me into changing it. :>(
When b=a, then the equation is just 2*a*Pi anyway.
So, the above is not necessary either.
Sub TestIt()
'// Ellipse Perimeter
Debug.Print Perimeter(10000, 9975)
Debug.Print Perimeter(9975, 10000)
Debug.Print
Debug.Print Perimeter(10000, 10000)
Debug.Print 2 * [Pi()] * 10000
End Sub
Returns:
62753.3378298691
62753.3378298691
62831.8530717959
62831.8530717959
Function Perimeter(a, b)
Dim k As Double
k = 3 * ((a - b) / (a + b)) ^ 2
Perimeter = (a + b) * (1 + k / (10 + Sqr(4 - k))) * [Pi()]
End Function
= = = = = =
Dana DeLouis