software

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

The CHITEST worksheet function is quot;sometimesquot; 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?

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 egt;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
members.aol.com/iandjmsmith/examples.xls

Jerry

quot;fred_y_Ohioquot; wrote:

gt; I am experiencing the same problem in both Excel 2002 and 2003.
gt;
gt; The CHITEST worksheet function is quot;sometimesquot; returning #NUM! instead of a
gt; number.
gt;
gt; CHITEST syntax is CHITEST(actual_range,expected_range)
gt;
gt; I am running CHITEST against 6 ranges of numbers - 10,000 numbers in each
gt; range. The expected range is always the same, for all of my CHITEST
gt; instances.
gt;
gt; One of the six ranges is returning #NUM!, the other 5 return a number(as I
gt; expect).
gt;
gt; I looked up the meaning of #NUM! - one listed cause is that some of the
gt; arguments are not numbers.
gt;
gt; I ran the COUNT function against the range - COUNT thinks they are all
gt; numbers.
gt;
gt; So, I ran CHITEST on a some sub-ranges of the range where CHITEST returns
gt; #NUM!.
gt;
gt; Example:
gt;
gt; Range of numbers RESULT
gt;
gt; 1-10,000 #NUM!
gt;
gt; 1-30 number
gt; 1-100 number
gt; 1-1000 number
gt;
gt; 1-2000 #NUM!
gt;
gt; 1001-2000 #NUM!
gt;
gt; 1001-1500 number
gt; 1501-2000 number
gt;
gt; I am stumped about why CHITEST would return a number for the sub-ranges
gt; 1001-1500 AND 1501-2000 but NOT for the sub-range 1001-2000.
gt;
gt; What am I missing?

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 quot;undocumented FEATUREquot;.)

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

quot;Jerry W. Lewisquot; wrote:

gt; You have not given enough information to diagnose the problem. As a guess,
gt; your data puts you into an area where CHIDIST fails.
gt;
gt; Help for CHITESTdocuements the calculation for the chi-square statistic and
gt; its degrees of freedom. For more information, reply back with the calculated
gt; chi-square statistic and degrees of freedom, if you don't want to reply back
gt; with the actual data.
gt;
gt; Those are passed to CHIDIST to evaluate the p-value. However
gt; CHIDIST(df-e,df) returns #VALUE instead of a p-value near 0.5 for even
gt; moderately large df and small egt;0. For example, =CHIDIST(799,800) returns
gt; #NUM.
gt;
gt; If you manually calculate the chi-square statistic and degrees of freedom,
gt; you could use comp_cdf_chi_sq() from Ian Smith's probability function library
gt; at
gt; members.aol.com/iandjmsmith/examples.xls
gt;
gt; Jerry
gt;
gt; quot;fred_y_Ohioquot; wrote:
gt;
gt; gt; I am experiencing the same problem in both Excel 2002 and 2003.
gt; gt;
gt; gt; The CHITEST worksheet function is quot;sometimesquot; returning #NUM! instead of a
gt; gt; number.
gt; gt;
gt; gt; CHITEST syntax is CHITEST(actual_range,expected_range)
gt; gt;
gt; gt; I am running CHITEST against 6 ranges of numbers - 10,000 numbers in each
gt; gt; range. The expected range is always the same, for all of my CHITEST
gt; gt; instances.
gt; gt;
gt; gt; One of the six ranges is returning #NUM!, the other 5 return a number(as I
gt; gt; expect).
gt; gt;
gt; gt; I looked up the meaning of #NUM! - one listed cause is that some of the
gt; gt; arguments are not numbers.
gt; gt;
gt; gt; I ran the COUNT function against the range - COUNT thinks they are all
gt; gt; numbers.
gt; gt;
gt; gt; So, I ran CHITEST on a some sub-ranges of the range where CHITEST returns
gt; gt; #NUM!.
gt; gt;
gt; gt; Example:
gt; gt;
gt; gt; Range of numbers RESULT
gt; gt;
gt; gt; 1-10,000 #NUM!
gt; gt;
gt; gt; 1-30 number
gt; gt; 1-100 number
gt; gt; 1-1000 number
gt; gt;
gt; gt; 1-2000 #NUM!
gt; gt;
gt; gt; 1001-2000 #NUM!
gt; gt;
gt; gt; 1001-1500 number
gt; gt; 1501-2000 number
gt; gt;
gt; gt; I am stumped about why CHITEST would return a number for the sub-ranges
gt; gt; 1001-1500 AND 1501-2000 but NOT for the sub-range 1001-2000.
gt; gt;
gt; gt; What am I missing?

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).
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

quot;fred_y_Ohioquot; wrote:

gt; Based on Jerry's advice,...
gt;
gt; For my set of 10,000 numbers, using Excel's CHITEST help documentation, I
gt; manually calculated the chi-square statistic (9,717) and degrees of freedom
gt; (9,999).
gt;
gt; I then used those values as parameters for Excel's CHIDIST function,
gt; CHIDIST(9717,9999). Result from Excel is #NUM! .
gt;
gt; Excel's help documentation for CHITEST and CHIDIST do not state any
gt; limitations for those two functions' parameters.
gt;
gt; Smells like a bug to me.......
gt; ....(....or is Microsoft gonna dismiss this an quot;undocumented FEATUREquot;.)
gt;
gt; Conclusion: CHITEST and CHIDIST do not always work as documented.
gt;
gt; quot;Jerry W. Lewisquot; wrote:
gt;
gt; gt; You have not given enough information to diagnose the problem. As a guess,
gt; gt; your data puts you into an area where CHIDIST fails.
gt; gt;
gt; gt; Help for CHITESTdocuements the calculation for the chi-square statistic and
gt; gt; its degrees of freedom. For more information, reply back with the calculated
gt; gt; chi-square statistic and degrees of freedom, if you don't want to reply back
gt; gt; with the actual data.
gt; gt;
gt; gt; Those are passed to CHIDIST to evaluate the p-value. However
gt; gt; CHIDIST(df-e,df) returns #VALUE instead of a p-value near 0.5 for even
gt; gt; moderately large df and small egt;0. For example, =CHIDIST(799,800) returns
gt; gt; #NUM.
gt; gt;
gt; gt; If you manually calculate the chi-square statistic and degrees of freedom,
gt; gt; you could use comp_cdf_chi_sq() from Ian Smith's probability function library
gt; gt; at
gt; gt; members.aol.com/iandjmsmith/examples.xls
gt; gt;
gt; gt; Jerry
gt; gt;
gt; gt; quot;fred_y_Ohioquot; wrote:
gt; gt;
gt; gt; gt; I am experiencing the same problem in both Excel 2002 and 2003.
gt; gt; gt;
gt; gt; gt; The CHITEST worksheet function is quot;sometimesquot; returning #NUM! instead of a
gt; gt; gt; number.
gt; gt; gt;
gt; gt; gt; CHITEST syntax is CHITEST(actual_range,expected_range)
gt; gt; gt;
gt; gt; gt; I am running CHITEST against 6 ranges of numbers - 10,000 numbers in each
gt; gt; gt; range. The expected range is always the same, for all of my CHITEST
gt; gt; gt; instances.
gt; gt; gt;
gt; gt; gt; One of the six ranges is returning #NUM!, the other 5 return a number(as I
gt; gt; gt; expect).
gt; gt; gt;
gt; gt; gt; I looked up the meaning of #NUM! - one listed cause is that some of the
gt; gt; gt; arguments are not numbers.
gt; gt; gt;
gt; gt; gt; I ran the COUNT function against the range - COUNT thinks they are all
gt; gt; gt; numbers.
gt; gt; gt;
gt; gt; gt; So, I ran CHITEST on a some sub-ranges of the range where CHITEST returns
gt; gt; gt; #NUM!.
gt; gt; gt;
gt; gt; gt; Example:
gt; gt; gt;
gt; gt; gt; Range of numbers RESULT
gt; gt; gt;
gt; gt; gt; 1-10,000 #NUM!
gt; gt; gt;
gt; gt; gt; 1-30 number
gt; gt; gt; 1-100 number
gt; gt; gt; 1-1000 number
gt; gt; gt;
gt; gt; gt; 1-2000 #NUM!
gt; gt; gt;
gt; gt; gt; 1001-2000 #NUM!
gt; gt; gt;
gt; gt; gt; 1001-1500 number
gt; gt; gt; 1501-2000 number
gt; gt; gt;
gt; gt; gt; I am stumped about why CHITEST would return a number for the sub-ranges
gt; gt; gt; 1001-1500 AND 1501-2000 but NOT for the sub-range 1001-2000.
gt; gt; gt;
gt; gt; gt; What am I missing?