jueves, 14 de agosto de 2008

Simulación de una Distribución Normal

En Excel disponemos de una función de distribución que genera directamente valores ajustados a una distribución normal, pero en esta ocasión vamos a generarlos sin recurrir a las funciones de Excel. La denominada transformada de Box-Müller permite generar una distribución Normal. Para ello se utilizan dos distribuciones Uniformes [0;1], que en Excel se obtienen con la función:

=ALEATORIO()


La función es la siguiente:

=+RAIZ((-2*LN(ALEATORIO())))*SENO(2*PI()*ALEATORIO())


Podríamos programar una función que nos de una Normal de media mu y desviación típica sigma.


Código:

Function xNORMAL(mu, sigma)
Dim NORMAL01
Const Pi As Double = 3.14159265358979
Randomize
NORMAL01 = Sqr((-2 * LN(Rnd))) * Sin(2 * Pi * Rnd)
xNORMAL = mu + sigma * NORMAL01
End Function

Function LN(x)
LN = Log(x) / Log(Exp(1))
End Function

Por ejemplo, la función =xNORMAL(100;2) nos proporcionará un número aleatorio que se ajusta a una distribución normal de media 100 y desviación típica 2.

11 comentarios:

  1. Interesante forma... pero hay una forma un poco mas simple:

    =DISTR.NORM.INV(ALEATORIO();media;desviacion)

    y lo arrastras ;)

    ResponderEliminar
  2. La forma que indicas es el método clásico.

    La novedad de la transformada de Box-Müller es que desde una macro, simplemente utilizando código VBA no es necesario recurrir a la hoja de cálculo, ni a sus funciones predefinidas para conseguir la Normal Inversa.

    ResponderEliminar
  3. Muchas gracias por tu publicación! ya no tengo q usar el objeto excel para sacar la normal inversa. Creo q hay unas librerias para .NET que incluyen una función NormalInverse(), pero no las encontré por ningún sitio, seguramente no sean gratuitas..

    Un saludo.

    ResponderEliminar
  4. Adolfo Aparicio: Por favor me interesa mucho usar tu algoritmo. Requiero también por favor si me puedes facilitar tu mail para hacerte unas preguntitas sobre este método que estas usando. Es el metodo de la transformada inversa o de box muller? Necesito un poco mas de informacion para poder documentar el uso de este algoritmo. Te lo agradecería infinitamente
    !!!
    Mil gracias de antemanos
    Saludos desde Chile

    ResponderEliminar
  5. Hola Magdalena.

    La transformada de Box-Muller la tienes en la wikipedia:

    http://en.wikipedia.org/wiki/Box%E2%80%93Muller_transform

    Mi correo es: financieras@gmail.com

    Un saludo.

    ResponderEliminar
  6. Si quisiera hacer una NORMAL Truncada???

    ResponderEliminar
  7. Hola Jorge.
    Para hacer una normal truncada puedes utilizar un condicional IF para truncarla.
    Un saludo.

    ResponderEliminar
  8. Respuestas
    1. Hola David.
      mu es la media, es como se designa habitualmente la media en estadística, y sigma es la desviación típica.

      Eliminar
  9. Hola.
    El código supongo que se realizó para la versión de Excel 2003. Ahora, lo he probado en Excel 2010, y el código continúa funcionando estupendamente.
    Es necesario programar la función LN mediante código ya que en VBA no existe, pero si existe LOG. Aunque ya sabemos que en Excel existen ambas, pero en VBA solo existe LOG que es el logaritmo en base decimal.
    Otra opción que hubiera evitado tener que programar LN hubiera sido llamar desde el código a la función LN de la siguiente forma:

    NORMAL01 = Sqr((-2 * Application.WorksheetFunction.LN(Rnd))) * Sin(2 * Pi * Rnd)

    Un saludo.

    ResponderEliminar