Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChiInv function
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChiInv function
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChiInv function
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... -----Original Message----- 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" wrote in message ... 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChiInv function
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 "Stef C" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChiInv function
"Stef C" wrote in message ...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apparent bug in CHIINV! | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
CHIINV error | Excel Worksheet Functions | |||
CHIINV error | Excel Worksheet Functions | |||
CHIINV error | Excel Worksheet Functions |