Pi

J

Jake

Notwithstanding the fact that Excel will only display a certain number of
decimal places, is it possible to, with formula or macro, calculate pi in
Excel to a significant number of places?

I am aware of =PI(), however, this is not relevant to the discussion.

Thanks...
 
R

Rick Rothstein

Notwithstanding the fact that Excel will only display a certain
number of decimal places, is it possible to, with formula or
macro, calculate pi in Excel to a significant number of places?

I am aware of =PI(), however, this is not relevant to the discussion.
Click to expand...

There is no formula for PI (see 2nd paragraph of this article...
http://en.wikipedia.org/wiki/Pi), so in the sense I think you are asking,
the answer would be no. However, if there was a maximum number of digits
that you would accept as a limit, then you could simply assign the value of
PI to that many digits to a constant (using a Const statement) within a
function and have the function return the left number of characters you want
at the time you use the function. For example, the following function will
return PI up to 1000 digits after the decimal point (remember, you won't be
able to do any accurate calculations with over 14 decimal places, 28 for
Variants of Decimal data Type provided you protect against over-flowing)...

Function PI(NumberOfDecimalPlaces As Long) As String
Const PIvalue As String =
"3.14159265358979323846264338327950288419716939937510" & _
"58209749445923078164062862089986280348253421170679"
& _
"82148086513282306647093844609550582231725359408128"
& _
"48111745028410270193852110555964462294895493038196"
& _
"44288109756659334461284756482337867831652712019091"
& _
"45648566923460348610454326648213393607260249141273"
& _
"72458700660631558817488152092096282925409171536436"
& _
"78925903600113305305488204665213841469519415116094"
& _
"33057270365759591953092186117381932611793105118548"
& _
"07446237996274956735188575272489122793818301194912"
& _
"98336733624406566430860213949463952247371907021798"
& _
"60943702770539217176293176752384674818467669405132"
& _
"00056812714526356082778577134275778960917363717872"
& _
"14684409012249534301465495853710507922796892589235"
& _
"42019956112129021960864034418159813629774771309960"
& _
"51870721134999999837297804995105973173281609631859"
& _
"50244594553469083026425223082533446850352619311881"
& _
"71010003137838752886587533208381420617177669147303"
& _
"59825349042875546873115956286388235378759375195778"
& _
"18577805321712268066130019278766111959092164201989"
If NumberOfDecimalPlaces <= 1000 Then
PI = Left(PIvalue, NumberOfDecimalPlaces)
Else
Err.Raise 6
End If
End Function

Rick Rothstein (MVP - Excel)
 
R

Rick Rothstein

Here is the function reformatted so it does not wrap badly...

Function PI(NumberOfDecimalPlaces As Long) As String
Const PIvalue As String = "3.141592653589793238462643383279502884" & _
"1971693993751058209749445923078164062862089986280348253421" & _
"1706798214808651328230664709384460955058223172535940812848" & _
"1117450284102701938521105559644622948954930381964428810975" & _
"6659334461284756482337867831652712019091456485669234603486" & _
"1045432664821339360726024914127372458700660631558817488152" & _
"0920962829254091715364367892590360011330530548820466521384" & _
"1469519415116094330572703657595919530921861173819326117931" & _
"0511854807446237996274956735188575272489122793818301194912" & _
"9833673362440656643086021394946395224737190702179860943702" & _
"7705392171762931767523846748184676694051320005681271452635" & _
"6082778577134275778960917363717872146844090122495343014654" & _
"9585371050792279689258923542019956112129021960864034418159" & _
"8136297747713099605187072113499999983729780499510597317328" & _
"1609631859502445945534690830264252230825334468503526193118" & _
"8171010003137838752886587533208381420617177669147303598253" & _
"4904287554687311595628638823537875937519577818577805321712" & _
"268066130019278766111959092164201989"
If NumberOfDecimalPlaces <= 1000 Then
PI = Left(PIvalue, NumberOfDecimalPlaces)
Else
Err.Raise 6
End If
End Function

Rick Rothstein (MVP - Excel)
 
R

Ron Rosenfeld

Notwithstanding the fact that Excel will only display a certain number of
decimal places, is it possible to, with formula or macro, calculate pi in
Excel to a significant number of places?

I am aware of =PI(), however, this is not relevant to the discussion.

Thanks...
Click to expand...

So long as your significant number is 15 or less, the PI() function works perfectly OK.

If you want very large signifcant numbers, then I would suggest the PiFast program:

http://numbers.computation.free.fr/Constants/PiProgram/pifast.html
 
J

Jake

Rick Rothstein said:
There is no formula for PI (see 2nd paragraph of this article...
http://en.wikipedia.org/wiki/Pi), so in the sense I think you are asking,
the answer would be no.
Click to expand...

Thank you for your response Rick.

Nevertheless, there are various programs available to compute pi to many
digits, so various iterative formulas must exist to enable them to carry out
this computation, it is simply a matter of defining the number of iterations
in the formula.

For example, is it possible to tell Excel to compute the n'th term in a
Fibonacci sequence starting 1,1,2,3,5....where n is a very large number? Or
even a very large number of these in sequence?

Expanding from this, would it then be possible to tell Excel to compute the
n'th decimal place of pi, where n is a very large number? Or even n decimal
places, as the more iterative calculations you use, the more decimal places
of pi can be achieved.
 
J

joeu2004

Nevertheless, there are various programs available to
compute pi to many digits, so various iterative formulas
must exist to enable them to carry out this computation,
it is simply a matter of defining the number of iterations
in the formula.
Click to expand...

No, Excel per se does not do iterative calculations in that way.

You would have to implement a well-known algorithm in a UDF using VBA
or some other language (C#?).

But note that that UDF will have to return a string (text), not a
number, if you want pi to be accurate to more than 16 decimal places.

In Excel, numbers are represented by 64-bit floating-point, and that
has inherent limitations. Excel and VBA format numbers only to 15
significant digits. So, for example, 0.1234 is formatted as such no
matter how many decimal places you specify.

But I have a UDF that returns the exactly decimal value. For example,
0.1234 is actually represented internally as
0.12339999999999999580335696691690827719867229461669921875.

You could do something similar to evaluate pi to as many decimal
places that you want.
 
R

Rick Rothstein

Nevertheless, there are various programs available to
compute pi to many digits, so various iterative formulas
must exist to enable them to carry out this computation,
it is simply a matter of defining the number of iterations in the formula.
Click to expand...
For example, is it possible to tell Excel to compute the
n'th term in a Fibonacci sequence starting 1,1,2,3,5....
where n is a very large number? Or even a very large
number of these in sequence?

Expanding from this, would it then be possible to tell
Excel to compute the n'th decimal place of pi, where
n is a very large number? Or even n decimal places,
as the more iterative calculations you use, the more
decimal places of pi can be achieved.
Click to expand...

Yes, there are power series that can be used to do this calculation, but it
is not a formula that can be calculated straight-away, rather, it is an
iterative process which takes longer and longer to finish the more decimal
places you want it to return. And on a desktop computer, we are not
necessarily talking minutes or even hours, especially for large numbers of
decimal places. Just how many decimal places did you want to calculate out
to? Is a million enough? If so, I would still use the approach I gave you
earlier except change the value assigned to the constant to the number found
here (after you remove the "neatening up" spaces and line feeds from it)...

http://newton.ex.ac.uk/research/qsystems/collabs/pi/pi6.txt

Rick Rothstein (MVP - Excel)
 
J

James Silverton

Jake wrote on Mon, 24 Jan 2011 04:41:29 -0000:


If you want to play with using Excel to calculate PI (which I don't
recommend), you might care to look at
http://mathworld.wolfram.com/PiFormulas.html . The site is pretty
comprehensive but, of course as others have pointed out, some very
ingenious programming has been done in other languages,

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not
 
J

Jake

Rick Rothstein said:
Yes, there are power series that can be used to do this calculation, but
it is not a formula that can be calculated straight-away, rather, it is an
iterative process which takes longer and longer to finish the more decimal
places you want it to return. And on a desktop computer, we are not
necessarily talking minutes or even hours, especially for large numbers of
decimal places. Just how many decimal places did you want to calculate out
to? Is a million enough? If so, I would still use the approach I gave you
earlier except change the value assigned to the constant to the number
found here (after you remove the "neatening up" spaces and line feeds from
it)...

http://newton.ex.ac.uk/research/qsystems/collabs/pi/pi6.txt

Rick Rothstein (MVP - Excel)
Click to expand...

Thanks, I only wanted 10,000-100,000 digits of pi.
 
S

Stan Brown

Notwithstanding the fact that Excel will only display a certain number of
decimal places, is it possible to, with formula or macro, calculate pi in
Excel to a significant number of places?
Click to expand...

Well, of course it's *possible*, depending on your definition of
"significant". It's also possible to drive a nail with a
screwdriver, though it's not the right tool for the job.
 
S

Stan Brown

There is no formula for PI (see 2nd paragraph of this article...
http://en.wikipedia.org/wiki/Pi),
Click to expand...

That paragraph says there's no exact value as fraction or decimal,
which is true. It does not say there's no formula, which is good
because that would be false. In fact there are many formulas for pi.
You would have seen one of them if you had scanned down a bit further
to the section "Estimating pi".

You can see many formulas for pi at
http://mathworld.wolfram.com/PiFormulas.html
 
S

Stan Brown

For example, is it possible to tell Excel to compute the n'th term in a
Fibonacci sequence starting 1,1,2,3,5....where n is a very large number? Or
even a very large number of these in sequence?
Click to expand...

Excel 2007 and 2010 have million-row spreadsheets, and it's not hard
to write (in A3) =A1+A2, then fill downward. I don't know how large
the millionth one would be, but I imagine it would be shown in
scientific notation and not exactly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PI Add-In 4
How to Create an S-Curve from Custom Field? 0
Minimum Distance Calculation using Array and Geographical Coordinates 1
Excel does not accept text format! 0
Formula problem 3
Automate the combo box 0
Formula problem 1
Help calculating scaled commissions please 5

Top