GAMMAINV not working

Topics: Developer Forum
Dec 29, 2009 at 4:46 AM

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 12: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 8:30 PM

Improved version of the Newton-Raphson 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 Newton-Raphson 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.

 

Jan 9, 2010 at 6:43 PM

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 7: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

Jan 10, 2010 at 5:24 PM

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 8: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