Calculate the Circumference of an ellipse


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

New Reply
Thread Tools Rate Thread Display Modes
  #1  
Unread 24th November 2009, 17:18
M's Avatar
M
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Calculate the Circumference of an ellipse
     Excel: General   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: General   

Is there a formula to Calculate the Circumference of an ellipse in excel?

The Code Cage Advertisment
Advertisement
  #2  
Unread 24th November 2009, 17:33
Paul C's Avatar
Paul C
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


There is not a standard formula for this. Someone may know of one available
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/for...umference.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.


"M" wrote:

> Is there a formula to Calculate the Circumference of an ellipse in excel?


  #3  
Unread 24th November 2009, 17:36
David Biddulph's Avatar
David Biddulph
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


You'll find one approximation at
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-8898-9430E0D48F29@microsoft.com...
> Is there a formula to Calculate the Circumference of an ellipse in excel?




The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #4  
Unread 24th November 2009, 22:15
JLatham's Avatar
JLatham
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


Disclaimer: I do not hold a degree in mathematics (heck, I flunked college
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.


"M" wrote:

> Is there a formula to Calculate the Circumference of an ellipse in excel?


  #5  
Unread 25th November 2009, 00:13
Dana DeLouis's Avatar
Dana DeLouis
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


Hi J. Maybe we can expand your excellent reference to a Custom
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



JLatham wrote:
> Disclaimer: I do not hold a degree in mathematics (heck, I flunked college
> 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.
>
>
> "M" wrote:
>
>> Is there a formula to Calculate the Circumference of an ellipse in excel?


  #6  
Unread 25th November 2009, 08:02
David Biddulph's Avatar
David Biddulph
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


What you did your last formula, Dana, was just work out the circumference of
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" <ddelouis@bellsouth.net> wrote in message
news:eSMAtNWbKHA.5656@TK2MSFTNGP04.phx.gbl...
> Hi J. Maybe we can expand your excellent reference to a Custom 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
>
>
>
> JLatham wrote:
>> Disclaimer: I do not hold a degree in mathematics (heck, I flunked
>> college 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.
>>
>>
>> "M" wrote:
>>
>>> Is there a formula to Calculate the Circumference of an ellipse in
>>> excel?




The Code Cage Advertisment
Advertisement
  #7  
Unread 25th November 2009, 21:55
JLatham's Avatar
JLatham
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


Dana,
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:

> Hi J. Maybe we can expand your excellent reference to a Custom
> 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
>
>
>
> JLatham wrote:
> > Disclaimer: I do not hold a degree in mathematics (heck, I flunked college
> > 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.
> >
> >
> > "M" wrote:
> >
> >> Is there a formula to Calculate the Circumference of an ellipse in excel?

> .
>


  #8  
Unread 25th November 2009, 23:39
JLatham's Avatar
JLatham
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


I won't say how confused I am at this point; as it seems amazing to me that a
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:

> Hi J. Maybe we can expand your excellent reference to a Custom
> 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
>
>
>
> JLatham wrote:
> > Disclaimer: I do not hold a degree in mathematics (heck, I flunked college
> > 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.
> >
> >
> > "M" wrote:
> >
> >> Is there a formula to Calculate the Circumference of an ellipse in excel?

> .
>


  #9  
Unread 25th November 2009, 23:53
David Biddulph's Avatar
David Biddulph
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


You may need to read the Wikipedia piece again to resolve your confusion.
The table shows Pr, the perimetric radius. The circumference is 2*PI()*Pr.
--
David Biddulph

"JLatham" <JLatham@discussions.microsoft.com> wrote in message
news:E67FDB04-2702-4760-A0C4-02A53EA6C2BA@microsoft.com...
>I won't say how confused I am at this point; as it seems amazing to me that
>a
> 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:
>
>> Hi J. Maybe we can expand your excellent reference to a Custom
>> 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
>>
>>
>>
>> JLatham wrote:
>> > Disclaimer: I do not hold a degree in mathematics (heck, I flunked
>> > college
>> > 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.
>> >
>> >
>> > "M" wrote:
>> >
>> >> Is there a formula to Calculate the Circumference of an ellipse in
>> >> excel?

>> .
>>




The Code Cage Advertisment
Advertisement
  #10  
Unread 26th November 2009, 00:07
Dana DeLouis's Avatar
Dana DeLouis
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: Calculate the Circumference of an ellipse

------ Register to get rid of these "In Post" ads! ------


> I won't say how confused I am at this point... has a perimeter of
> '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



On 11/25/09 6:31 PM, JLatham wrote:
> I won't say how confused I am at this point; as it seems amazing to me that a
> 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:
>
>> Hi J. Maybe we can expand your excellent reference to a Custom
>> 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
>>
>>
>>
>> JLatham wrote:
>>> Disclaimer: I do not hold a degree in mathematics (heck, I flunked college
>>> 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.
>>>
>>>
>>> "M" wrote:
>>>
>>>> Is there a formula to Calculate the Circumference of an ellipse in excel?

>> .
>>


The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

New Reply
The Code Cage at Facebookhe Code Cage at Twitter


Tags
calculate, circumference, ellipse

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 18:09.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University