La generación de valores aleatorios en Excel está limitada a las distribuciones que se encuentran disponibles para ser modeladas (Normal, Beta, Binomial, entre otras). Por ejemplo, la siguiente fórmula permite generar números aleatorios distribuidos normalmente en torno a una media y con una desviación estándar especificadas:

=NORM.INV(ALEATORIO(),media,desviacion)

En muchos modelos con pronósticos representados por variables aleatorias, sin embargo, se requiere una distribución sencilla y acotada que permita establecer valores mínimos, máximos y esperados. La Distribución Triangular es la que mejor se acomoda a este requerimiento, pero no se encuentra disponible entre las funciones de Excel.

El siguiente programa en VBA soluciona este problema, creando una nueva función TRIANGULAR(probabilidad, mínimo, máximo, esperado) que genera números aleatorios distribuidos de la forma deseada. El núcleo del algoritmo se basa en las ecuaciones:

Se comienza calculando un número k que representa la posición relativa del punto b (valor esperado) en el intervalo [a,c], por lo cual b pertenecerá al intervalo [0,1]. Luego se genera un valor aleatorio p que también estará entre [o,1[ y se verifica si está antes o después del valor esperado. Si p es menor que k, el valor aleatorio se sitúa a la derecha de a y como función de p. Si p es mayor que k, el valor aleatorio se sitúa a la izquierda de c y como función de (1-p).

De esta forma, la implementación en VBA es muy sencilla y se reduce al siguiente código que define la función TRIANGULAR (no olvidar insertar el código en un Módulo para que pueda ser invocada desde Excel).

Public Function TRIANGULAR(a As Double, b As Double, c As Double, _
   P As Single) As Double

   Dim k As Single
   Dim X As Double

   'Calculamos k
   k = (b - a) / (c - a)

   If P < k Then
      X = a + Sqr(P * (b - a) * (c - a))
   Else
      X = c - Sqr((1 - P) * (b - a) * (c - a))
   End If

   TRIANGULAR = X

End Function

En la planilla de ejemplo vemos un posible uso de la función recién creada, para proyectar valores inciertos de cantidad, costo y margen unitario. Se dispone en las celdas F5 y G5 de los valores máximos y mínimos respectivamente, mientras que en E5 se encuentra el valor esperado. El cuarto parámetro es el valor aleatorio entre [0,1[ que lo generamos mediante la función ALEATORIO() de Excel. La ventaja de hacerlo de esta manera es que generaremos nuevos valores aleatorios cada vez que presionemos F9.

En cambio, si quisiéramos que el valor aleatorio quedara estático (no se modificara en nuestra planilla al presionar al hacer nuevos cálculos), la solución está en eliminar este cuarto parámetro y calcular dentro del procedimiento el valor de P, insertando simplemente la instrucción.

P = RND()

¡Cuéntame qué nuevos usos o mejoras se te vienen en mente!