CHIINV - Degree of freedom error

[Tom Anderson]

Statistical function: chiinv.

Using a formula to calculate the degrees of freedom:

2 * (B12 + 1) where B12 is a cell reference and having a
value > 462 reults in an error #NUM! for any probability

=chiinv(0.1, 2 * (B12 + 1)) (Fails if value in B12 > 462)

Replacing the formula, with a reference to a cell - only,
the same result.

=chiinv(0.1, B12) --- Cell B12 has value of 926 ----FAILS

By using exact values, the functions returns properly:
=chiinv(0.1, 926) ----- THIS WORKS.
This last method also works for values > 926.

 

[Harlan Grove]

...
...

2 * (B12 + 1) where B12 is a cell reference and having a
value > 462 reults in an error #NUM! for any probability

=chiinv(0.1, 2 * (B12 + 1)) (Fails if value in B12 > 462)

Replacing the formula, with a reference to a cell - only,
the same result.

=chiinv(0.1, B12) --- Cell B12 has value of 926 ----FAILS

By using exact values, the functions returns properly:
=chiinv(0.1, 926) ----- THIS WORKS.
This last method also works for values > 926.

I can't reproduce your results under Excel 97 SR-2 running under Windows NT4
SP6. What version of Excel are you using? Are you on a Mac?

 

[Tom Anderson]

This is the case for Excel 2002 (XP) and also Mac:Excel for OSX (Mac
OSX version) - I have verified both.

Regards,

Tom Anderson

 

[Jerry W. Lewis]

I cannot get failure (Excel XP SP-2) with 926 df, but I do for 928 df.

Although CHIDIST works for df this large, CHIINV does not (and the
equivalent GAMMAINV formulation fails for even smaller df. The
algorithms MS chose for distribution formulas give limited accuracy over
a limited working range, and the inverse functions are even worse.

For df this large, you can do reasonably well (3-figures for your
example) with the Normal approximation
=928+NORMSINV(0.9)*sqrt(2*928)
That could be improved by using the Cornish-Fisher expansion
(Technometrics 2(2):209-225,1960), which adds more terms

=df +z*sqrt(2*df) +(z^2-1)*2/3 +z*(z^2-7)/9/sqrt(2*df)
-(6*z^4+14*z^2-32)/405/df +...

where for your problem, z = NORMSINV(0.9) = -NORMSINV(0.1). The terms
given above yield 7 figure accuracy for your problem. None of Excel's
inverse distribution functions can be relied on for better accuracy than
that (hopefully they will be improved in a future version of Excel).

If you don't mind running VBA functions,
http://members.aol.com/iandjmsmith/Examples.xls
has replacements for all of Excel's probability functions, and appears
to be quite good in accuracy and working range.

Jerry

 

[Tom Anderson]

If you don't mind running VBA functions,
http://members.aol.com/iandjmsmith/Examples.xls
has replacements for all of Excel's probability functions, and appears
to be quite good in accuracy and working range.

Jerry

Jerry -

Thanks for the VBA examples. Too bad the documentation is not up to
standards - meaning no recognition (or little) about the algorithm sources
for each function - just the code to generate the chosen values. AND the
lack of the ROUND function in some VBA renditions - an easy enough thing to
code.

Many would appreciate credit where credit is due.

Tom Anderson

 

[Ian Smith]

Jerry -

Thanks for the VBA examples. Too bad the documentation is not up to
standards - meaning no recognition (or little) about the algorithm sources
for each function - just the code to generate the chosen values. AND the
lack of the ROUND function in some VBA renditions - an easy enough thing to
code.

Many would appreciate credit where credit is due.

Tom Anderson

I'm very happy you've asked this question and only too happy to fill
you in on where the algorithms came from. The original work was done
by me from approx. 1992-1996.

The poisson pdf is mine. The poisson cdf continued fractions for the
tails of the poisson distribution are old and well known - I really
should find references to these. The gamma/poisson cdf asymptotic
expansion is mine I've recently published details of how it works in
sci.stat.math (The binomial pdf is mine. I developed the continued fraction for the
beta/binomial distributions myself although it may have been developed
by someone else (if so I'd love to hear about it). The beta/binomial
cdf asymptotic expansion is mine - I will publish details of how it
works soon. The calculations for the gamma and beta distributions for
small shape parameters simply rely on series expansions of exp(-x) for
the gamma and (1-x)^(b-1) for the beta prior to integration.

The hypergeometric pdf is mine as is the continued fraction formula
for the hypergeometric cdf. It's similar to the ones for the poisson
and binomial. Again if someone has developed this formula
independently I'd love to hear about it - in particular why no-one
else is using it, given that it is far superior to standard summation
techniques! Incidentally, the technique of using a continued fraction
formula to sum the tails and then conventional summation techniques to
sum the terms nearer the centre increases the performance both in
terms of speed and accuracy.

The algorithms for inverting the gamma and beta functions are mine.
Details of how they work can be found via the "limits" link. This
link also leads to details of how the poisson/binomial/hypergeometric
pdf functions are calculated.

I had my own inverse function for the normal but replaced it with
AS241 for speed reasons. I'm not a big fan of approximations functions
which require you to come up with an entirely different approximation
function if you want to alter the accuracy to which the approximation
holds. I find functions which rely on an accuracy value much more
appealing but I needed speed for inversion functions for someone else
and I don't think you can beat AS241 for the speed/accuracy
combination.

The "crit" functions were added so I could calculate Clopper-Pearson
confidence bounds for the hypergeometric distribution (again details
of how the bounds are calculated can be found via the "limits" link.)
The "crit" functions for other discrete distributions were added
mainly for consistency.

I think that covers all the function in Examples.xls and the only
references I'm missing are for the continued fraction formulae for the
tails of the poisson distribution. I will try and update the code with
this information in the near future.

Ian Smith