Excel y la Maquina de Galton

Recientemente, leyendo el blog del maestro Adolfo Aparicio encontré un post sobre la máquina de Galton, donde desarrollaba mediante macros el experimento de Francis Galton.

Me pareció interesante replicar mediante funciones el comportamiento de esta caja de Galton (ver Wikipedia)

La idea es demostrar como un comportamiento aleatorio sobre la posibilidad de tomar dos caminos conduce a una estructura de campana, tendente a la forma de una campana de Gauss o de una distribución normal.

haz clic en la imagen

Inicialmente generaremos una estructura de pirámide de 11 niveles, comenzando en la cúspide de esta, celda L4 con la fórmula:
=ALEATORIO.ENTRE(0;1)
esta función nos aporta el componente de azar necesario...

Desde de esa celda L4, siguiendo la pendiente izquierda, celdas:
$K$5,$J$6,$I$7,$H$8,$G$9,$F$10,$E$11,$D$12,$C$13 y $B$14
añadimos la fórmula:
=SI(L4=0;ALEATORIO.ENTRE(0;1);"")
es decir, si la celda anterior fuera 0 generamos un nuevo aleatorio 0-1

Desde la celda L4 y siguiendo la pendiente derecha, celda:
$M$5,$N$6,$O$7,$P$8,$Q$9,$R$10,$S$11,$T$12,$U$13 y $V$14
añadimos la fórmula:
=SI(L4=1;ALEATORIO.ENTRE(0;1);"")
es decir, si la celda anterior fuera 1 generamos un nuevo aleatorio 0-1

Completamos el interior de nuestra pirámide aleatoria.
En las celdas alternas:
$L$6,
$K$7,$M$7,
$J$8,$L$8,$N$8,
$I$9,$K$9,$M$9,$O$9,
$H$10,$J$10,$L$10,$N$10,$P$10,
$G$11,$I$11,$K$11,$M$11,$O$11,$Q$11,
$F$12,$H$12,$J$12,$L$12,$N$12,$P$12,$R$12,
$E$13,$G$13,$I$13,$K$13,$M$13,$O$13,$Q$13,$S$13,
$D$14,$F$14,$H$14,$J$14,$L$14,$N$14,$P$14,$R$14,$T$14
con la fórmula en L6:
=SI(O(K5=1;M5=0);ALEATORIO.ENTRE(0;1);"")

Ahora vamos a convertir los 0 y los 1 resultantes en una bolita roja que simula el camino hasta la base.

Seleccionamos el rango $B$4:$V$14 y aplicamos un formato condicional tipo Conjunto de iconos de tres bolas con la siguiente configuración:
+presionamos el botón de 'Invertir criterio de ordenación'
+marcamos la opción para 'Mostrar el icono únicamente' (no mostrar el valor de la celda)
+cambiar a tipo número ambos tramos y asignarles valoro cero en los dos casos

En el siguiente paso obtendremos el dato acumulado de llegadas de las 'bolitas' a cada punto de la base de la pirámide.
Para ello tendremos que cambiar las opciones de Excel respecto al cálculo (a Manual) y las opciones de iteración.
Si accedemos a la ficha Archivo > Opciones de Excel> menú Fórmulas en la primera sección de Opciones de cálculo configuramos:
1-Cálculo de libro: Manual
2-Habilitar cálculo iterativo: marcado
3-Iteraciones máximas: 1

Tras aceptar, podremos incorporar la fórmula que nos permite acumular llegadas...
En las celdas B17,D17,F17,H17,J17,L17,N17,P17,R17,T17 y V17 añadimos la fórmula:
=SI(B14<>"";B17+1;B17)
que nos permite autosumar a la cantidad existente esas celdas, las nuevas llegadas de las bolitas a la base...

Finalmente seleccionando las celdas anteriores de la fila 17 podemos insertar un gráfico de líneas, con la propiedad de línea suavizada para mejorar su aspecto.

Y estamos listos para comprobar la teoría de Francis Galton... puedes empezar a presionar la tecla de función F9 para recalcular el libro de trabajo.
Comprobaremos que según aumentamos el número de simulaciones, el gráfico toma apariencia de una distribución normal, i.e., concentro en los puntos interiores la mayoría de las llegadas...

En mi ejemplo, con unas 7.000 simulaciones, el aspecto es el siguiente: