Higher precision: can one get it?

[Richard Lionheart]

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.

 

[Niek Otten]

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

 

[Greg Lovern]

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.86720395054666447519702433469456109482178276232645703981220472
990158197209281613382250690200215 etc., up to 32,767 digits)
=xlpROOT(1.98,1.98)
(Returns
1.41198766954687879574014815720379054207623799583460566043557515
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

 

[Richard Lionheart]

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

 

[Dana DeLouis]

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]

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

 

[Richard Lionheart]

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 <[email protected]> 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/devobio/210labs/MeiosisQuiz/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

 

[Dana DeLouis]

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/mathematica/Demos/Notebooks/CalculatingPi.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

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 <[email protected]> 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/devobio/210labs/MeiosisQuiz/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

 

[Richard Lionheart]

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/mathematica/Demos/Notebooks/CalculatingPi.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.)

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