Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Apparent bug in CHIINV!

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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 762
Default Apparent bug in CHIINV!

Boris -

Is there a workaround? <


One possible workaround is to use Ian Smith's inv_chi_sq function, one of
his many VBA statistical functions.

His file can be downloaded from
http://members.aol.com/iandjmsmith/Examples.xls

And David Heiser has some notes at
http://www.daheiser.info/excel/notes/notead.pdf

- Mike
www.MikeMiddleton.com


"Boris" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Apparent bug in CHIINV!

Thanks Mike - that's very helpful. In the meantime, I have found that, for
large n, there is a fairly straghtforward linear relationship between n
(sample size) and the inverse chi square at any given probablity (and inverse
chi square at 50% comes out to be n+1 or v+2 where v is degrees of freedom).
However, probably best to actually calculate the chi square velaues I need so
I will definately try the function and have a look at the notes.
Many thanks and best wishes, Boris.

"Mike Middleton" wrote:

Boris -

Is there a workaround? <


One possible workaround is to use Ian Smith's inv_chi_sq function, one of
his many VBA statistical functions.

His file can be downloaded from
http://members.aol.com/iandjmsmith/Examples.xls

And David Heiser has some notes at
http://www.daheiser.info/excel/notes/notead.pdf

- Mike
www.MikeMiddleton.com


"Boris" wrote in message
...
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.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default Apparent bug in CHIINV!

For a more accurate assymptotic approximation, see equation 3a in
http://digital.library.adelaide.edu....fisher/281.pdf
In that equation, n is degrees of freecom, and x is inverse normal ordinate
for the same probability level. The full expression gives at least 5-figure
accuracy for the median (x=0) when df=4, increasing to about 10 figure
accuracy by df=100. The farther you go from the median, the slower the
expression converges, but it should be fine for large df unless you are
interested in extreme tail behavior.

The Smith library would give high accuracy everywhere.

Jerry

"Boris" wrote:

Thanks Mike - that's very helpful. In the meantime, I have found that, for
large n, there is a fairly straghtforward linear relationship between n
(sample size) and the inverse chi square at any given probablity (and inverse
chi square at 50% comes out to be n+1 or v+2 where v is degrees of freedom).
However, probably best to actually calculate the chi square velaues I need so
I will definately try the function and have a look at the notes.
Many thanks and best wishes, Boris.

"Mike Middleton" wrote:

Boris -

Is there a workaround? <


One possible workaround is to use Ian Smith's inv_chi_sq function, one of
his many VBA statistical functions.

His file can be downloaded from
http://members.aol.com/iandjmsmith/Examples.xls

And David Heiser has some notes at
http://www.daheiser.info/excel/notes/notead.pdf

- Mike
www.MikeMiddleton.com


"Boris" wrote in message
...
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.




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Apparent bug in CHIINV!

Dear Jerry,
Many thanks for the excellent reference. I have compared the Smith library
with Maple (v 8) and it does indeed do the trick. For my current analysis
(data sets of between 30k and 92k members) the fisher approach at 2.5 and
97.5% is great too but I think I will stick with a linear approximation as it
too gives good results (to several significant figures) in the range I need
it in. An even better and simple fit for n (sample size) = 1000 seems to be
a simple shifted power fit (y=a*(x-b)^c) with a around 2.7 (depending on the
probbility), b positive and about 17 for the low % and negative and about -15
for the high % point and c about 0.5.... (curve fits have an associated
standard error of around 0.024 and a correlation coeffficient of 1.000000).
However, the much more precise approach in Fisher and Cornish is really
useful.
Many thanks and best wishes, Boris

"Jerry W. Lewis" wrote:

For a more accurate assymptotic approximation, see equation 3a in
http://digital.library.adelaide.edu....fisher/281.pdf
In that equation, n is degrees of freecom, and x is inverse normal ordinate
for the same probability level. The full expression gives at least 5-figure
accuracy for the median (x=0) when df=4, increasing to about 10 figure
accuracy by df=100. The farther you go from the median, the slower the
expression converges, but it should be fine for large df unless you are
interested in extreme tail behavior.

The Smith library would give high accuracy everywhere.

Jerry

"Boris" wrote:

Thanks Mike - that's very helpful. In the meantime, I have found that, for
large n, there is a fairly straghtforward linear relationship between n
(sample size) and the inverse chi square at any given probablity (and inverse
chi square at 50% comes out to be n+1 or v+2 where v is degrees of freedom).
However, probably best to actually calculate the chi square velaues I need so
I will definately try the function and have a look at the notes.
Many thanks and best wishes, Boris.

"Mike Middleton" wrote:

Boris -

Is there a workaround? <

One possible workaround is to use Ian Smith's inv_chi_sq function, one of
his many VBA statistical functions.

His file can be downloaded from
http://members.aol.com/iandjmsmith/Examples.xls

And David Heiser has some notes at
http://www.daheiser.info/excel/notes/notead.pdf

- Mike
www.MikeMiddleton.com


"Boris" wrote in message
...
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.






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Apparent bug in CHIINV!

Dear Jerry,
I should have added that I am fitting n-chisq(n-1,0.025) and
chisq(n-1,0.975)-n to n. Could just have easily fitted chisq() against n but
that would give different constants for a, b, and c...
Best wishes, Boris.

"Boris" wrote:

Dear Jerry,
Many thanks for the excellent reference. I have compared the Smith library
with Maple (v 8) and it does indeed do the trick. For my current analysis
(data sets of between 30k and 92k members) the fisher approach at 2.5 and
97.5% is great too but I think I will stick with a linear approximation as it
too gives good results (to several significant figures) in the range I need
it in. An even better and simple fit for n (sample size) = 1000 seems to be
a simple shifted power fit (y=a*(x-b)^c) with a around 2.7 (depending on the
probbility), b positive and about 17 for the low % and negative and about -15
for the high % point and c about 0.5.... (curve fits have an associated
standard error of around 0.024 and a correlation coeffficient of 1.000000).
However, the much more precise approach in Fisher and Cornish is really
useful.
Many thanks and best wishes, Boris

"Jerry W. Lewis" wrote:

For a more accurate assymptotic approximation, see equation 3a in
http://digital.library.adelaide.edu....fisher/281.pdf
In that equation, n is degrees of freecom, and x is inverse normal ordinate
for the same probability level. The full expression gives at least 5-figure
accuracy for the median (x=0) when df=4, increasing to about 10 figure
accuracy by df=100. The farther you go from the median, the slower the
expression converges, but it should be fine for large df unless you are
interested in extreme tail behavior.

The Smith library would give high accuracy everywhere.

Jerry

"Boris" wrote:

Thanks Mike - that's very helpful. In the meantime, I have found that, for
large n, there is a fairly straghtforward linear relationship between n
(sample size) and the inverse chi square at any given probablity (and inverse
chi square at 50% comes out to be n+1 or v+2 where v is degrees of freedom).
However, probably best to actually calculate the chi square velaues I need so
I will definately try the function and have a look at the notes.
Many thanks and best wishes, Boris.

"Mike Middleton" wrote:

Boris -

Is there a workaround? <

One possible workaround is to use Ian Smith's inv_chi_sq function, one of
his many VBA statistical functions.

His file can be downloaded from
http://members.aol.com/iandjmsmith/Examples.xls

And David Heiser has some notes at
http://www.daheiser.info/excel/notes/notead.pdf

- Mike
www.MikeMiddleton.com


"Boris" wrote in message
...
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.




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 516
Default Apparent bug in CHIINV!

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.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 524
Default Apparent bug in CHIINV!

Problems with Excel's functions for statistical distributions have
been known and documented for a long time.

http://biostat.mc.vanderbilt.edu/twi.../ExcelProblems
http://www.daheiser.info/excel/frontpage.html

--
"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Apparent bug in CHIINV!

"Stan Brown" wrote...
Problems with Excel's functions for statistical distributions have
been known and documented for a long time.

http://biostat.mc.vanderbilt.edu/twi.../ExcelProblems
http://www.daheiser.info/excel/frontpage.html

....

This appears to be a continuation of a thread from a few weeks ago. See
Jerry Lewis's earlier response in this thread.


  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 516
Default Apparent bug in CHIINV!

So, is anyone at Microsoft planning on doing something about these bugs?

"Stan Brown" wrote:

Problems with Excel's functions for statistical distributions have
been known and documented for a long time.

http://biostat.mc.vanderbilt.edu/twi.../ExcelProblems
http://www.daheiser.info/excel/frontpage.html

--
"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default Apparent bug in CHIINV!

Matt wrote...
So, is anyone at Microsoft planning on doing something about these
bugs?

....

Don't plan on it. These have been know and published for over a
decade, so it doesn't seem MSFT feels much need to fix 'em. There are
add-ins that provide more accurate, though slower, alternative
functions. They're your only choice other than waiting a few more
decades for MSFT to get around to fixing them.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Apparent bug in CHIINV!

Hi Matt,
I agree - at the very least, Microsoft should update their hopelessly
inadequate help file entries and list the problem areas explicitly! That, I
would have thought, would be quite simple to do but...
Best wishes, Boris.

"Matt" wrote:

So, is anyone at Microsoft planning on doing something about these bugs?

"Stan Brown" wrote:

Problems with Excel's functions for statistical distributions have
been known and documented for a long time.

http://biostat.mc.vanderbilt.edu/twi.../ExcelProblems
http://www.daheiser.info/excel/frontpage.html

--
"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

  #13   Report Post  
Junior Member
 
Posts: 2
Default

The Ian Smith website at AOL is no longer available and I can not find an alternative. Is this still an issue with Excel 2007, and where can I find an up to date version of the functions?


Quote:
Originally Posted by Mike Middleton View Post

One possible workaround is to use Ian Smith's inv_chi_sq function, one of
his many VBA statistical functions.

His file can be downloaded from
http://members.aol.com/iandjmsmith/Examples.xls

And David Heiser has some notes at
http://www.daheiser.info/excel/notes/notead.pdf

- Mike
www.MikeMiddleton.com
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Apparent Locked Cursor SEREBS Excel Discussion (Misc queries) 1 July 14th 06 03:12 PM
Excel is behaving strangely for no apparent reason. canyondude New Users to Excel 4 January 10th 06 01:07 AM
CHIINV error Amy Bass Excel Worksheet Functions 1 November 3rd 04 03:06 PM
CHIINV error Amy Excel Worksheet Functions 1 November 2nd 04 08:09 PM
CHIINV error Amy Bass Excel Worksheet Functions 0 November 2nd 04 07:30 PM


All times are GMT +1. The time now is 03:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"