ChiInv function

[Stef C]

Hi,

I am using the ChiInv function of Excel and get an error
1004 when sending some values, but no error sending other
values... it do not seem to be an error from a certain
value (ex: start bugging when sending a value of 512 or
more...) it seem to be random, but I know that it is
not... anybody know this one.

 

[Tom Ogilvy]

Test your values in a worksheet using the formula in the worksheet (not in
VBA).

If you get a #Value or #Ref or something like that, check the Excel VBA help
and see what conditions cause that error to be displayed.

When you use WorksheetFunction.ChiInv, then a condition that would return a
# type error in the worksheet raises a 1004 error in VBA.

Regards,
Tom Ogilvy

 

[Stef C]

I have already done this kind of test, I use an excel
Sheet, put number 1 to 3500 in the "A" column, and get
the ChiInv solution on each values from 1 to 3500
(probability = 0.05) in the column "B", I get error on
value 818 but not 817 nor 819, it also bug on 928 to 948
values... there must be a logic behind that but cannot
catch it...

 

[David J. Braden]

Curious behavior. And try this in a worksheet: enter what, theoretically, is
the same thing:
=GAMMAINV(1-x,deg_freedom/2,2)
and you'll see that the latter is identical to CHIINV(x,deg_freedom) where
both don't return #NUM!, yet often one works where the other fails. So Excel
has a separate implementation for CHIINV. Weird.
Workaround would be (in pseudocode)
Dim vRes as variant, lDF as long, dX as Double, etc.
vRes = ChiInv(dX, lDF)
if lres is an error then
lRes = GammaInv(1-dX,lDF/2,2)
if lRes is an error then
'do something; linear or quartic interpolate if possible?
end if
end if

Another possibility: perhaps someone has done a better job of implementation
in an add-in.
Or, make a call to Mathematica or R (free open-source) from Excel.
HTH
Dave Braden
MVP

 

[Ian Smith]

Hi,

I am using the ChiInv function of Excel and get an error
1004 when sending some values, but no error sending other
values... it do not seem to be an error from a certain
value (ex: start bugging when sending a value of 512 or
more...) it seem to be random, but I know that it is
not... anybody know this one.

I suspect the seemingly quirky behaviour is due to convergence
problems, although, according to the help information, it should
return N/A if it doesn't converge within 100 iterations.

You are more than welcome to use the code in
http://members.aol.com/iandjmsmith/Examples.xls.

The functions associated with the gamma distribution (also used for
the poisson, chi-squared...) have been extensively tested for shape
parameters in the range 1e-12 to 1e7 and for probabilities from
0.999999999999999 to 3e-308. Testing for this was done independently
(and unknown to me at the time) by Jerry W. Lewis and I'm indebted to
him for all his efforts. I'm glad to say Jerry has since recommended
the code and also made several suggestions to improve the code.

Ian Smith

 

[Tushar Mehta]

This has been mentioned before. See

www.google.com/groups?selm=Rvmta.4334%24ls6.93896%40news.uswest.net

and

www.google.com/groups?selm=39BF9267.92AA65A6%40alcatel.de

I thought I'd read a MSKB article on the subject, but cannot find it
now.

It is possible the error is because the function uses an iterative
approach and for those specific values it gets trapped in an endless
pattern.

You might also want to check Jerry Lewis' suggestion at
www.google.com/groups?selm=3F176548.1010206%40no_e-mail.com

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions