
Hi, I am new to this. When I tried GAMMAINV function, the result was not even close to that of with Excel 2007! I am not sure the reason. GAMMADIST is correct but its inverse is not working.
Ex:
GAMMAINV(.5,8,9)=37.200833451847
GAMMAINV(.5,8,9)=69.02325 (excel)
GAMMAINV(1,8,9)=69.63217023903
GAMMAINV(1,8,9)=340.4438 (excel)
Thanks in advance for any solution :)


Coordinator
Jan 1, 2010 at 1:47 AM

Will take a look at this. I'm not happy with the algorithm used to calculate GAMMAINV anyway, but there's very little documented about how Excel itself calculates the function.


Coordinator
Jan 6, 2010 at 9:30 PM

Improved version of the NewtonRaphson for the GAMMAINV() function
public static function GAMMAINV($probability,$alpha,$beta) {
$probability = self::flattenSingleValue($probability);
$alpha = self::flattenSingleValue($alpha);
$beta = self::flattenSingleValue($beta);
if ((is_numeric($probability)) && (is_numeric($alpha)) && (is_numeric($beta))) {
if (($alpha <= 0)  ($beta <= 0)  ($probability < 0)  ($probability > 1)) {
return self::$_errorCodes['num'];
}
$xLo = 0;
$xHi = $alpha * $beta * 5;
$x = $xNew = 1;
$error = $pdf = 0;
$dx = 1024;
$i = 0;
while ((abs($dx) > PRECISION) && ($i++ < MAX_ITERATIONS)) {
// Apply NewtonRaphson step
$error = self::GAMMADIST($x, $alpha, $beta, True)  $probability;
if ($error < 0.0) {
$xLo = $x;
} else {
$xHi = $x;
}
$pdf = self::GAMMADIST($x, $alpha, $beta, False);
// Avoid division by zero
if ($pdf != 0.0) {
$dx = $error / $pdf;
$xNew = $x  $dx;
}
// If the NR fails to converge (which for example may be the
// case if the initial guess is too rough) we apply a bisection
// step to determine a more narrow interval around the root.
if (($xNew < $xLo)  ($xNew > $xHi)  ($pdf == 0.0)) {
$xNew = ($xLo + $xHi) / 2;
$dx = $xNew  $x;
}
$x = $xNew;
}
if ($i == MAX_ITERATIONS) {
return self::$_errorCodes['na'];
}
return $x;
}
return self::$_errorCodes['value'];
} // function GAMMAINV()
This is more accurate than the current version, even with higher values of $alpha and $beat; but I'm not yet satisfied with the value of the initial value of $xHi, so I won't check it into SVN just yet.



Hi Mark,
Appreciate your efforts! I am not sure if I got it right but still I am getting weird values compared to Excel 2007.
Here are some examples!!!
GAMMAINV(0.5, 1, 5) =2.02209606038503
GAMMAINV(0.5, 1, 5) Excel =3.46573594
GAMMAINV(0.5, 10, 5) =10
GAMMAINV(0.5, 10, 5)Excel =48.34357557
GAMMAINV(0.7, 10, 5) =10
GAMMAINV(0.7, 10, 5)Excel =56.93636269
I am really sorry I tried to fix this but have no clue what so ever!! Will keep trying tho :) Will let you know if I could figure it out :)
Again I really appreciate your efforts on this :)
Thanks :)


Coordinator
Jan 10, 2010 at 8:22 AM

I've just run your three examples through my test suite, and am getting the following results:
Formula is =GAMMAINV(0.5,1,5)
Excel Value is : 3.4657359402240715
PHPExcel Calculated Value is : 3.465735902799727
Formula is =GAMMAINV(0.5,10,5)
Excel Value is : 48.34357456828873
PHPExcel Calculated Value is : 48.34357307358031
Formula is =GAMMAINV(0.7,10,5)
Excel Value is : 56.936362687231991
PHPExcel Calculated Value is : 56.93636268416658
Not perfect results, but all accurate to at least 7 significant digits I've checked the code that produced those results into SVN



Oh great!! in that case there must be some problem from my side. Will look in to it :)
Thank you so much for such a wonderful information :)
And thanks for the confirmation :)
Will be in touch!!
You ROCK !. .!
Cheers


Coordinator
Jan 10, 2010 at 9:46 PM

My modified code should be available now in the latest SVN downloads, and I believe that Maarten is planning a 1.7.2 release for sometime in the coming week that will include the change

