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" wrote:

> 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?

[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" wrote:

> 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" wrote:
>
> > 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]

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

"fred_y_Ohio" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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?