How do I Calculate PI in an Excel formula?

Just for gee-wiz, here's an easy way to show how inaccurate GAMMALN is,
even at small values. This should zero out...

=EXP(GAMMALN(1/2))^2 - PI()

4.07633E-10

= = =
Dana DeLouis



Jerry W. Lewis wrote:
While Excel will only display 15 digits, its value for pi is correct to
almost 17 digits. To 17 digits, Excel's value for pi is
3.1415926535897931
compared to the actual 17 digit approximation to pi of
3.1415926535897932

Jerry

"Shane Devenshire" wrote:

....
PI has been calculated to 2 billion digits, probably more by now, but Excel
is not prepared for more than 15.
....

MS rarely used more than one algorithm for any math function that was not
provided by the math coprocessor. Ln(Gamma(x)) is usually calculated by an
asymptotic expansion
6.1.41 in http://www.math.sfu.ca/~cbm/aands/page_257.htm
or its related continued fraction
6.1.48 in http://www.math.sfu.ca/~cbm/aands/page_258.htm
that converges slowly (if at all) for small x, so it should be no surprise
that its accuracy improves as x becomes large.

If you have a copy of Smith's VBA library of probability functions, you can
go through the source code and see the lengths he went to to avoid these
problems for small x.

Jerry

"Dana DeLouis" wrote:

Just for gee-wiz, here's an easy way to show how inaccurate GAMMALN is,
even at small values. This should zero out...

=EXP(GAMMALN(1/2))^2 - PI()

4.07633E-10

= = =
Dana DeLouis

6.1.41 in http://www.math.sfu.ca/~cbm/aands/page_257.htm
or its related continued fraction
6.1.48 in http://www.math.sfu.ca/~cbm/aands/page_258.htm
that converges slowly (if at all) for small x, so it should be no surprise
that its accuracy improves as x becomes large.

Thanks Jerry for the links. Always an interesting subject. :)


(Side note...here's a test for larger x values)

In [A1]... then copied down:
=EXP(GAMMALN(ROW())) - FACT(ROW()-1)

snip