0
$\begingroup$

The GAMMAINV function from Excel 2003 had a propensity for generating NA error messages for high alpha values as the iterative process failed to reach convergence. It was therefore common to use a normal approximation if alpha > 300 (the gamma distribution tends towards the normal distribution for high alpha).

In 2010 Microsoft refurbished many of the Excel statistical functions, including the gamma distribution, now called GAMMA.INV. The iterative search algorithm have supposedly been improved.

Q1: Should the NORM.INV still be called in situations where alpha > 300?

I did some analyses trying to input extreme parameters that should/could lead to failed convergence. I found that GAMMAINV and GAMMA.INV returned exactly the same results (also with respect to errors). I expected that there should at least some minor numeric differences between them...

Q2: Does this mean that the GAMMAINV calls on the same algorithm as GAMMA.INV in Excel 2010?

$\endgroup$
6
  • 3
    $\begingroup$ No doubt you are aware of the pitfalls here. Absence of evidence of difference is not evidence of absence of difference. Not clear what anybody can add to this unless they have found any differences in results themselves; the code is manifestly not public. Have you asked Microsoft? (People are at liberty to regard that as amusing.) $\endgroup$
    – Nick Cox
    Jan 16 '15 at 11:58
  • $\begingroup$ Dear @NickCox, Thank you for you comment. I took the liberty to use your post as a motivation to contact Microsoft support. Neither the 1st or 2nd line tech support was able to answer. They did, however, recomend posting on the question on answers.microsoft.com. Ill post an update as soon as hear something from MS. $\endgroup$ Jan 16 '15 at 13:24
  • $\begingroup$ Why don't you just compare the output to published tables or reliable statistical software? $\endgroup$
    – whuber
    Jan 16 '15 at 15:56
  • $\begingroup$ @whuber, im using the gammainv in a Monte Carlo simulation; consuliting a stat table for every iteration is slightly tedious. Using "proper" statistical software would defenitely be preferable, but i have to use Excel because it was a requirement from the agency that ordered the model i'm preparing. $\endgroup$ Jan 16 '15 at 20:48
  • $\begingroup$ I am not suggesting you do this for your work. Where I wrote "compare" I meant only that you spend a couple of minutes generating a range of typical values in Excel and compare them against what ought to be the same values as found in a table or produced by other software. That would answer Q1. I don't see any point even to investigating Q2 unless you have to use legacy spreadsheets with legacy versions of Excel. $\endgroup$
    – whuber
    Jan 16 '15 at 20:51
0
$\begingroup$

In excel, the inverse gamma distribution need 3 parameters (probability, alpha and beta). To test potential differences between the GAMMAINV and the GAMMA.INV I designed two 2x2 tables (one for the GAMMAINV and one for the GAMMA.INV) where the y-axis contained probabilities ranging from 0,01 to 1 and the x-axis contained beta-values ranging from 0,01 to 1 (1 to 100 were also tested). This generated a table containing 10 000 draws for each version of the gammainv respectively. The alpha parameter where then varied for both tables with an arbritary selection of values between 1.00-E100 and 1.00+E100.

Observation: No differences between the GAMMAINV and the GAMMA.INV were detected and, further, no error messages indicated that excels iterative procedure had failed to reach convergence where present.

A similar analysis were conducted for the GAMMA.INV and the NORM.INV. In this case some minor differences between the distributions where detected. The largest difference between them was 1.48. This is approx 0.5% of the cut-off value of 300 as suggested in the method outlined above. The absolute difference seem constant across different values of the alpha parameter and its relative size diminish with increasing numbers of alpha.

Conclusion: In this simple analysis, i where unable to detect evidence that would motivate a substitution of the GAMMA.INV with the NORM.INV in excel 2010. Further, no differences between using the old GAMMAINV and the new GAMMA.INV commands were detected, this might suggest that they both call on the new algorithm.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.