How do I Calculate PI in an Excel formula?

[Dana DeLouis]

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]

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]

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>