1

Gamma_inv returns an error if the random number passed to it is higher than roughly 0.96. This happens in both Excel and with VBA. I am running a Monte Carlo, and this only happens 10 or so times in over 40,000 runs. Here is how I am using it. alpha and beta are always >0. If "gammatrunc"= 1, I get the errors. If I set it to 0.95, I get no errors:

     alpha = B * B * bsy2 ^ -2
     beta = bsy2 * bsy2 / B
     rand = Rnd * gammatrunc
     B = WorksheetFunction.GAMMA_Inv(rand, alpha, beta)

The errors look like this:

Output with error messages

I'd appreciate any thoughts on why this is happening.

Thanks

Don Scavia
  • 11
  • 3

1 Answers1

0
You can use this function to study a variable whose distribution may be skewed.

If any argument is text, Gamma_Inv returns the #VALUE! error value.

If probability < 0 or probability > 1, Gamma_Inv returns the #NUM! error value.

If alpha ≤ 0 or if beta ≤ 0, Gamma_Inv returns the #NUM! error value.

Given a value for probability, Gamma_Inv seeks that value x such that GAMMA_DIST(x, alpha, beta, TRUE) = probability. Thus, precision of Gamma_Inv depends on precision of Gamma_Dist. Gamma_Inv uses an iterative search technique. If the search has not converged after 100 iterations, the function returns the #N/A error value.

https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.gamma_inv

You can add some controls in your code, like MIN and MAX, using VBA.

ASH
  • 20,759
  • 19
  • 87
  • 200
  • I've seen this documentation and as my attachment shows, alpha and beta are both >0 and rand is between 0 and 1. I still get these errors in a small number of calls. I supposed I could try min and max to double check those values. Any additional thoughts? – Don Scavia May 06 '20 at 15:24
  • I was just throwing it out there. Honestly, I have never heard of 'GAMMA_Inv' until today. – ASH May 06 '20 at 15:27