Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
Hi All,
I demonstrating the approximation of Pi by successively computing the perimeters of regular inscribed polygons with 2^n sides. Excel's precision was overwhelmed at about 4000 sides. Is there any way to get "double precision"? If worse comes to worse, I'll use something like BigDecimal in Ruby or Perl to get greater precision. -- Regards, Richard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
Hi Richard,
Excel's precision is 15 significant digits. If you need more, you might take a look at the XNUMBERS add-in: http://digilander.libero.it/foxes/MultiPrecision.htm -- Kind regards, Niek Otten "Richard Lionheart" wrote in message ... Hi All, I demonstrating the approximation of Pi by successively computing the perimeters of regular inscribed polygons with 2^n sides. Excel's precision was overwhelmed at about 4000 sides. Is there any way to get "double precision"? If worse comes to worse, I'll use something like BigDecimal in Ruby or Perl to get greater precision. -- Regards, Richard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
XNumbers can help with demonstrating the approximation of Pi by succesively
computing the perimeters of regular inscribed polygons with 2^n sides. But for anything requiring fractional powers or fractional roots, you'll need something else, because XNumbers truncates fractional powers and fractional roots to integers. Compare XNumbers to Excel: Excel: =1.98^1.98 (Returns 3.86720395054666) =1.98^(1/1.98) (Returns 1.41198766954688) XNumbers: =xpow(1.98,1.98) (Returns 1.98) =xroot(1.98,1.98) (Returns 1.98) My add-in xlPrecision 2.0 returns fractional powers and fractional roots with up to 32,767 significant digits of precision: =xlpPOWER(1.98,1.98) (Returns 3.867203950546664475197024334694561094821782762326 45703981220472 990158197209281613382250690200215 etc., up to 32,767 digits) =xlpROOT(1.98,1.98) (Returns 1.411987669546878795740148157203790542076237995834 60566043557515 9344161656315787429344740444142505 etc., up to 32,767 digits) You can download the free edition of xlPrecision 2.0 here, and use it as long as you wish: http://PrecisionCalc.com Thanks, Greg Lovern http://PrecisionCalc.com Get Your Numbers Right "Niek Otten" wrote in message ... Excel's precision is 15 significant digits. If you need more, you might take a look at the XNUMBERS add-in: http://digilander.libero.it/foxes/MultiPrecision.htm "Richard Lionheart" wrote in message ... I demonstrating the approximation of Pi by successively computing the perimeters of regular inscribed polygons with 2^n sides. Excel's precision was overwhelmed at about 4000 sides. Is there any way to get "double precision"? If worse comes to worse, I'll use something like BigDecimal in Ruby or Perl to get greater precision. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
Hi Niek and Greg ,
Thank you very much for your responses. I downloaded the XNumbers addin and got great results. I showed my 12yo grandson the algebra for calculating perimiters of regular inscribed polygons using recursion and showed him the results in Excel. We compared these with published estimates of Pi to hundreds of places. Got a favorable comparison up to 10 places using 60 places for intermediate results and 32 iiterations. I'll check out xlPrecision a little later. Right now I've only got integral exponents to deal with. Best wishes, Richard |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
Hi. For the op, ... Is there any way to get "double precision"?
Not sure if you would find this interesting... If you would like to "double" your precision, here is one method to calculate Pi using ArcTan and just built-in functions. This is not the fastest convergence, but it is simple. It reaches Excel's limit in 20 loops. Maybe you can adopt it to your method. If you would like to get real crazy, then Excel has a Fast Fourier Transform function in the analysis toolpak that you can use to multiply large numbers very quickly. However, Excel's built-in FFT is limited to 4096 digits. Sub TestIt() Debug.Print "Pi= " & Pi End Sub Function Pi() As Variant '// = = = = = = = = = = = = = = = = = = = = = '// Pi = 16*ArcTan(1/5) - 4*ArcTan(1/239) '// By: Dana DeLouis '// = = = = = = = = = = = = = = = = = = = = = Dim One Dim Two Dim d5 Dim d239 Dim p1 Dim p2 Dim f Dim j As Long One = CDec(1) Two = One + One d5 = One / 5 d239 = One / 239 f = One '// The first loop w/ j=0 p1 = d5 p2 = d239 Pi = Pi + (4 * f * (4 * p1 - p2)) / One f = -f '// Then... For j = 1 To 19 p1 = p1 * d5 * d5 p2 = p2 * d239 * d239 Pi = Pi + (4 * f * (4 * p1 - p2)) / (One + j * Two) f = -f Next j End Function HTH. :) -- Dana DeLouis Win XP & Office 2003 "Richard Lionheart" wrote in message ... Hi Niek and Greg , Thank you very much for your responses. I downloaded the XNumbers addin and got great results. I showed my 12yo grandson the algebra for calculating perimiters of regular inscribed polygons using recursion and showed him the results in Excel. We compared these with published estimates of Pi to hundreds of places. Got a favorable comparison up to 10 places using 60 places for intermediate results and 32 iiterations. I'll check out xlPrecision a little later. Right now I've only got integral exponents to deal with. Best wishes, Richard |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
If interested, here's a newer version. I removed 5 multiplications per
loop, for a savings of about 95+ Multiplications. Here's the output from the immediate window. The last digit will usually be off a little. Real Pi= 3.1415926535897932384626433832795.... My Pi= 3.1415926535897932384626433834 Again, not the fastest convergence, just one of the simplier versions for Excel. -- Dana DeLouis Win XP & Office 2003 Sub TestIt() Debug.Print "Real Pi= " & "3.1415926535897932384626433832795...." Debug.Print " My Pi= " & Pi End Sub Function Pi() As Variant '// = = = = = = = = = = = = = = = = = = = = = '// Pi = 16*ArcTan(1/5) - 4*ArcTan(1/239) '// By: Dana DeLouis '// Note: Pi = 3.1415926535897932384626433832795... '// = = = = = = = = = = = = = = = = = = = = = Dim Two Dim Den Dim d5 Dim d239 Dim p1 Dim p2 Dim j As Long Den = CDec(1) Two = CDec(2) d5 = Den / 5 d239 = Den / 239 '// First loop w/ j=0 p1 = d5 p2 = d239 Pi = (4 * (4 * p1 - p2)) / Den '// Newer values... d5 = d5 * d5 d239 = d239 * d239 '// Then... For j = 1 To 10 p1 = p1 * d5 p2 = p2 * d239 Den = Den + Two Pi = Pi - (4 * (4 * p1 - p2)) / Den p1 = p1 * d5 p2 = p2 * d239 Den = Den + Two Pi = Pi + (4 * (4 * p1 - p2)) / Den Next j End Function -- Dana DeLouis Win XP & Office 2003 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
Hi Dana,
Thank you very much for the worked-out example: an excellent result. I presume that you are, like me, a mathematician. But if I were to use inverse trig functions, I would have started with Pi = 8 * arctan(1). But I suspect that will converge rather slowly compared to the expression you constructed, whose correctness is not obvious to me. But no matter: I'll pop it into Excel and appreciate the result. I'm using XP-Pro/SP2 and Office Pro 2003 Below, in detail, is what I wanted and what I got. I'm writing this narrative mainly as a review for my grandson and forwarding it to you in case you have an academic interest in the matter. The boy called me the other day because he wanted to earn some money for Christmas and knows that I pay him and his siblings whenever they're interested in studying advanced (for their age) mathematics (or physics, etc.). We negotiated a price of $2/hr, but in fact renegotiated after the first hour to $4.50/hr for two additional hours: a king's ransom J My purpose in seeking additional precision in Excel was to demonstrate to my 12-yo grandson that determining Pi's value was accessible to even a child with little more knowledge than elementary algebra and some Geometry, like Pythagorean Theorem, and a "reasonable" computing tool for the drudgery. He had the requisite algebra. So we spent a half-hour or so studying a beautiful proof of the P.T. at http://www.cut-the-knot.org/pythagoras/index.shtml (Proof #2). We just had to learn SAS congruence theorem, the Parallel Postulate and the theorem that a transversal cutting two parallel lines leads to equality of alternate interior angles. With a couple of reviews, I believe he'll have internalized this result. Then we worked out the first approximation of Pi from an inscribed square, then from an inscribed octagon and so on. That led to recursive results: Pi = (2^n) * S(2 - S(2+S(2+...))), where S is "square root" and n is the number of two's in the right-hand factor. He followed this derivation pretty well, and I expect in after a couple of reviews, he'll be able to reproduce it on his own. The I showed him the Excel version of that calculation using XNumbers that Niek Otten put me on to. That produced good results that the boy could take on faith. Finally, I backed it up with a comparison with a published result from http://www.uoguelph.ca/zoology/devob...isQuiz/pi.html. It recomputed a table of 30 cycles with a precision of 60 place instantly on my 2GHz Pentium with 500MB RAM. All in all, a very gratifying result. In fact, we went further that afternoon. I guided him in computing the slope at various points of y=x^2 using [f(x+?x) - f(x)] / ?x. I derived a couple of identities for differentiating polynomials and gave him a few more to accept on faith. He spent the final hour differentiating simple polynomials. I was most pleased by the fact that he prove to himself that Dx(2x2 * 3x3) could be computed as Dx(6x5) and thus 30x4, or as 2x2 * Dx(3x3) + Dx(2x2) * 3x3. When I tell him that I think he's gifted in mathematics, he thinks my opinions are based solely on my love for him. That's kids for you J. Best wishes, Richard Muller |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
Hi. I find these math problems interesting also. Using the Math add-inn is
the way to go. However, I was just showing how one can get a little "higher precision" when calculating Pi using Excel. It's not really obvious that it can be done at all. (...that's why we all hang out here :) Actually, I became interested in this a long time ago while studying Fourier Analysis. I was studying Excel's Fourier function and learning how to do large multiplications / divisions very fast via Excel's Fast Fourier Transorm. A good test for speed was calculating Pi. Not sure, but you may find this article interesting reading. http://documents.wolfram.com/mathema...ulatingPi.html Pi = (2^n) * S(2 - S(2+S(2+...))), Although this in not the same equation, here's a very similar equation. (Vieta's equation from above link) Output is: Real Pi= 3.1415926535897932384626433832795.... My Pi= 3.1415926535897932384626433827 Sub Pi_Vietas_Formula() '// = = = = = = = = = = = = = = = = = = = = = '// Vieta's Formula for Pi '// 2/Pi = 0.5*Sqrt(2)* (0.5*Sqrt(2+Sqrt(2)))* (0.5*Sqrt(2+Sqrt(2+Sqrt(2)))) .... '// By: Dana DeLouis '// Note: Pi = 3.1415926535897932384626433832795... '// = = = = = = = = = = = = = = = = = = = = = Dim n Dim t Dim x Dim Pi Dim j As Long x = CDec(0) Pi = x + 1 For j = 0 To 45 n = 2 + x t = CDec(Sqr(n)) x = (t * t + n) / (2 * t) Pi = Pi * x / 2 Next j Pi = 2 / Pi Debug.Print "Real Pi= " & "3.1415926535897932384626433832795...." Debug.Print " My Pi= " & Pi End Sub -- Dana DeLouis Win XP & Office 2003 "Richard Lionheart" wrote in message ... Hi Dana, Thank you very much for the worked-out example: an excellent result. I presume that you are, like me, a mathematician. But if I were to use inverse trig functions, I would have started with Pi = 8 * arctan(1). But I suspect that will converge rather slowly compared to the expression you constructed, whose correctness is not obvious to me. But no matter: I'll pop it into Excel and appreciate the result. I'm using XP-Pro/SP2 and Office Pro 2003 Below, in detail, is what I wanted and what I got. I'm writing this narrative mainly as a review for my grandson and forwarding it to you in case you have an academic interest in the matter. The boy called me the other day because he wanted to earn some money for Christmas and knows that I pay him and his siblings whenever they're interested in studying advanced (for their age) mathematics (or physics, etc.). We negotiated a price of $2/hr, but in fact renegotiated after the first hour to $4.50/hr for two additional hours: a king's ransom J My purpose in seeking additional precision in Excel was to demonstrate to my 12-yo grandson that determining Pi's value was accessible to even a child with little more knowledge than elementary algebra and some Geometry, like Pythagorean Theorem, and a "reasonable" computing tool for the drudgery. He had the requisite algebra. So we spent a half-hour or so studying a beautiful proof of the P.T. at http://www.cut-the-knot.org/pythagoras/index.shtml (Proof #2). We just had to learn SAS congruence theorem, the Parallel Postulate and the theorem that a transversal cutting two parallel lines leads to equality of alternate interior angles. With a couple of reviews, I believe he'll have internalized this result. Then we worked out the first approximation of Pi from an inscribed square, then from an inscribed octagon and so on. That led to recursive results: Pi = (2^n) * S(2 - S(2+S(2+...))), where S is "square root" and n is the number of two's in the right-hand factor. He followed this derivation pretty well, and I expect in after a couple of reviews, he'll be able to reproduce it on his own. The I showed him the Excel version of that calculation using XNumbers that Niek Otten put me on to. That produced good results that the boy could take on faith. Finally, I backed it up with a comparison with a published result from http://www.uoguelph.ca/zoology/devob...isQuiz/pi.html. It recomputed a table of 30 cycles with a precision of 60 place instantly on my 2GHz Pentium with 500MB RAM. All in all, a very gratifying result. In fact, we went further that afternoon. I guided him in computing the slope at various points of y=x^2 using [f(x+?x) - f(x)] / ?x. I derived a couple of identities for differentiating polynomials and gave him a few more to accept on faith. He spent the final hour differentiating simple polynomials. I was most pleased by the fact that he prove to himself that Dx(2x2 * 3x3) could be computed as Dx(6x5) and thus 30x4, or as 2x2 * Dx(3x3) + Dx(2x2) * 3x3. When I tell him that I think he's gifted in mathematics, he thinks my opinions are based solely on my love for him. That's kids for you J. Best wishes, Richard Muller |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Higher precision: can one get it?
Hi Dana,
while studying Fourier Analysis. I was fascinated with Fourier Transforms also, but that was in the late 50's when I was in college [Queens College, New York City], before I ever heard of electronic computers. http://documents.wolfram.com/mathema...ulatingPi.html Thanks for the link. I love the articles. I often thought of buying Mathematica's package, but didn't because I dropped out of math after quitting graduate school because computer programming was much more exciting. I only do math with my grandchildren now. BTW, are there any open-source/freeware packages on the 'Net for writing mathematical expressions? I'm using MS Word's Drawing toolbar for documenting for my grandson what we did the other day (to allow him to review it when working on future problems.) Pi = (2^n) * S(2 - S(2+S(2+...))), Although this in not the same equation, here's a very similar equation. (Vieta's equation from above link) Although Vieta's equation is very neat, it's my guess that his work is not very well known. I fell in love with math as a grammar school sophmore, when a teacher gave me a book on algebra (probably to keep me from interrupting her in math lectures.) But I fell deeply in love when I discovered Number Theory as a member of the math team in high school. Euler and Gauss were my heros. My equation met my goal of being understandable by an elementary school kid without any mathematical training beyond elementary algebra. I was impressed (but not surprised) that Archimedes used essentially the same appoach to compute ? as the ratio of the areas of the unit circle and unit rectangle. That I used a sinilar approach only proves I had a good education. Sub Pi_Vietas_Formula() Thanks for the encoding of Vieta's formula as an Excel subroutine. I'll run it and show it to my grandson when I compare it to my formula as we jack up the precision and number of iterations. When we run it on his (older and slower) machine, we should find a perceptible performance difference. Best wishes, Richard Muller |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Precision as displayed problems | Excel Worksheet Functions | |||
Is there a way to count higher than 511 in binary in excel? | Excel Worksheet Functions | |||
Precision displayed does not match precision in cell | Excel Discussion (Misc queries) | |||
comparing a value in a cell to see if it is higher than a number | Excel Worksheet Functions | |||
Precision as displayed | Setting up and Configuration of Excel |