I have just discovered this chiinv bug myself with some data I was trying to
compute a standard deviation confidence interval. The problem happens as
early as df=782. In fact, you can see a very nice picture of a list of
approximately all the values this function explodes if you list percentiles
in the top row (ie: a1=0.01, b1=a1+0.01, etc to 0.99), then place the
following formula in the next row:
=CHIINV(A$1,ROW()+700).

Fill in the matrix and look at what starts to happen at around row 82
(df=782) and beyond. There are huge sections (especially around 50% like you
mentioned Boris) that destroy the algorithm. If you zoom the spreadsheet out
to 25%, you get a nice picture of the regions you can't use this algorithm.
Either the bug needs to be fixed, or the exclusion regions need to be
identified in the help. :-) )

Matt


"Boris" wrote:

Hi,
I am having some real problems with the CHIINV function in Excel (I am using
Excel 2007 but the same seems to be true of all previous versions of Excel -
at least back to 2002 - too). According to the help file, CHIINV can take
probabilities = 0 and <= 1 (just as well that it can - though there seems to
be an issue with the 50% probability) and degrees of freedom up to but not
including 10^10 (ie 10 billion). However, when you try to calculate a chiinv
of, say 0.05 probability, and you set the degrees of freedom to anything
above 1806, the formula immediately returns #NUM! Now, 1806 is much much
smaller than 10^10 so should be perfectly legitimate. In my application in
need to generate chiinv for degrees of freedom up to 90000 (still much less
than the 10^10 given in the function reference) and so I am really getting
stuck...
Is this a bug? Is there a workaround? Anyone have any suggestions for what
may be limiting the chiinv degrees of freedom to <=1806 and why this
particular number should be the limiting value?
I would really appreciate any hints (and confirmation that this error is
persistent across platforms and versions of Excel as I have only been able to
try it on a few machines running Excel XP, 2003 or 2007). If anyone knows of
a clever way to work around this apparent problem, that would be extremely
interesting...
Many thanks and best wishes, Boris.