CHITEST worksheet function - bug?

[fred_y_Ohio]

I am experiencing the same problem in both Excel 2002 and 2003.

The CHITEST worksheet function is "sometimes" returning #NUM! instead of a
number.

CHITEST syntax is CHITEST(actual_range,expected_range)

I am running CHITEST against 6 ranges of numbers - 10,000 numbers in each
range. The expected range is always the same, for all of my CHITEST
instances.

One of the six ranges is returning #NUM!, the other 5 return a number(as I
expect).

I looked up the meaning of #NUM! - one listed cause is that some of the
arguments are not numbers.

I ran the COUNT function against the range - COUNT thinks they are all
numbers.

So, I ran CHITEST on a some sub-ranges of the range where CHITEST returns
#NUM!.

Example:

Range of numbers RESULT

1-10,000 #NUM!

1-30 number
1-100 number
1-1000 number

1-2000 #NUM!

1001-2000 #NUM!

1001-1500 number
1501-2000 number

I am stumped about why CHITEST would return a number for the sub-ranges
1001-1500 AND 1501-2000 but NOT for the sub-range 1001-2000.

What am I missing?

 

[Jerry W. Lewis]

You have not given enough information to diagnose the problem. As a guess,
your data puts you into an area where CHIDIST fails.

Help for CHITESTdocuements the calculation for the chi-square statistic and
its degrees of freedom. For more information, reply back with the calculated
chi-square statistic and degrees of freedom, if you don't want to reply back
with the actual data.

Those are passed to CHIDIST to evaluate the p-value. However
CHIDIST(df-e,df) returns #VALUE instead of a p-value near 0.5 for even
moderately large df and small e>0. For example, =CHIDIST(799,800) returns
#NUM.

If you manually calculate the chi-square statistic and degrees of freedom,
you could use comp_cdf_chi_sq() from Ian Smith's probability function library
at
http://members.aol.com/iandjmsmith/examples.xls

Jerry

 

[fred_y_Ohio]

Based on Jerry's advice,...

For my set of 10,000 numbers, using Excel's CHITEST help documentation, I
manually calculated the chi-square statistic (9,717) and degrees of freedom
(9,999).

I then used those values as parameters for Excel's CHIDIST function,
CHIDIST(9717,9999). Result from Excel is #NUM! .

Excel's help documentation for CHITEST and CHIDIST do not state any
limitations for those two functions' parameters.

Smells like a bug to me.......
.....(....or is Microsoft gonna dismiss this an "undocumented FEATURE".)

Conclusion: CHITEST and CHIDIST do not always work as documented.

 

[Jerry W. Lewis]

I agree that CHIDIST (and hence CHITEST) should be able to handle this
calculation, but thus far, MS has not seen fit to use a better algorithm. As
I noted previously, there is a freely availabe VBA library of probability
functions that are as good or better than any double precision implementation
I have seen (including those in dedicated statistics packages and commercial
numerical libraries).
http://members.aol.com/iandjmsmith/examples.xls
Using that library, =comp_cdf_chi_sq(9717,9999) returns 0.977703672596211,
which is correct to all figures that Excel can display.

Jerry