Función DESREF de Excel para descifrar el tesoro Beale

Hace algún tiempo ya hice una entrada sobre esta historia que está expuesta en este comic de Cryptoclub. Los papeles con las cifras están en wikipedia. Tres hojas y solo una resuelta.

Las hojas contienen un conjunto de números. La solución conocida para la segunda es asociar cada número con una palabra de la declaración de independencia de los Estados Unidos de la época y quedarnos solo con la inicial. En su momento realicé programas en LOGO y Python como método de intento de resolución porque son muy versátiles para introducir diferencias en la manera en que podrían estar cifradas las hojas.

Se me ocurrió pensar si con una hoja de cálculo se podría implementar algo sencillo y la función DESREF resultó ser útil a este respecto. Combinándola con la función IZQUIERDA (que toma solo la primera letra de un texto) se puede usar de esta manera:

El uso sería el siguiente:

La primera columna (columna A) contiene solo los números de una de las hojas (en este caso la segunda que está resuelta), un número por fila.

La segunda columna (columna B) contiene el texto contra el que lo queremos confrontar (en este caso la declaración de independencia de los Estados Unidos) tal y como está resuelta la segunda hoja y también una palabra por fila.

En la tercera columna (columna C) escribimos la fórmula en la primera celda:

=IZQUIERDA(DESREF($A$1;A1-1;1))

y el resto autocompletamos.

Podemos observar que nos da el conocido texto «I have deposited…» (con los errores conocidos).

El modo de funcionamiento de DESREF en este caso es sencillo: tomo como origen siempre la primera celda (A1), de ahí que usemos referencias absolutas ($A$1). Nos desplazamos hacia abajo tanto como nos dice el número de la columna A (restando 1 para que todo cuadre), de ahí el uso de (A1-1) que lo hacemos relativo para el uso de autocompletar. El último número de DESREF hace mención a que debe desplazarse uno hacia la derecha desde el origen (es decir, que coja la columna B que contiene el texto ordenado).

Como curiosidad dejo la columna D con el resultado de coger la última letra de cada palabra en vez de la primera, sin más que cambiar la función IZQUIERDA por la función DERECHA.

Triángulo de Pascal en python y convergencia del cociente de los números centrales

El triángulo de Pascal presenta muchas curiosidades. Fijándome en los números centrales (1,2,6,20,70,252, etc.) me pareció que el cociente de dos de estos números sucesivos convergían al número cuatro o a algún número cercano a cuatro. Podemos ver que:

2/1 = 2
6/2 = 3
20/6 = 3.33
70/20 = 3.5
252/70 = 3.6

Así que aproveché para hacer un par de pequeños programas en python sobre el particular. En primer lugar un programa para sacar las filas del triángulo (empezando a partir de las cuarta fila, dado por sabida la tercera que es 1 2 1):

#programa para calcular el triangulo de Pascal
fila_antigua = [1,2,1]
numero = 20
for i in range (1,numero):
lon_antigua = len(fila_antigua)
fila_nueva = [1]
for j in range(0,lon_antigua-1):
calculo = fila_antigua[j]+fila_antigua[j+1]
fila_nueva.append(calculo)
fila_nueva.append(1)
print(fila_nueva)
fila_antigua = fila_nueva

El resultado son las filas del triángulo:

[1, 3, 3, 1]
[1, 4, 6, 4, 1]
[1, 5, 10, 10, 5, 1]
[1, 6, 15, 20, 15, 6, 1]
[1, 7, 21, 35, 35, 21, 7, 1]
[1, 8, 28, 56, 70, 56, 28, 8, 1]
[1, 9, 36, 84, 126, 126, 84, 36, 9, 1]
[1, 10, 45, 120, 210, 252, 210, 120, 45, 10, 1]
[1, 11, 55, 165, 330, 462, 462, 330, 165, 55, 11, 1]
[1, 12, 66, 220, 495, 792, 924, 792, 495, 220, 66, 12, 1]
[1, 13, 78, 286, 715, 1287, 1716, 1716, 1287, 715, 286, 78, 13, 1]
[1, 14, 91, 364, 1001, 2002, 3003, 3432, 3003, 2002, 1001, 364, 91, 14, 1]

Con pocas modificaciones en el programa calculamos el cociente entre estos números centrales:

#programa para calcular el triangulo de Pascal
#queremos calculas proporciones entre el valor medio
fila_antigua = [1,2,1]
numero = 40000
centro2 = 2
centro1 = 1
for i in range (1,numero):
lon_antigua = len(fila_antigua)
fila_nueva = [1]
for j in range(0,lon_antigua-1):
calculo = fila_antigua[j]+fila_antigua[j+1]
fila_nueva.append(calculo)
fila_nueva.append(1)
#print(fila_nueva)
print (centro2/centro1)
if (lon_antigua%2) == 0:
centro1 = centro2
centro2 = fila_nueva[int(len(fila_nueva)/2)]
#print(centro2)
fila_antigua = fila_nueva

Hemos calculado con 40000 filas. Los últimos resultados son estos:

3.999899964987746
3.999899964987746
3.9998999699909974
3.9998999699909974
3.9998999749937485
3.9998999749937485
3.999899979995999
3.999899979995999
3.9998999849977497
3.9998999849977497
3.9998999899989998
3.9998999899989998
3.99989999499975
3.99989999499975
3.9999

Se ve claramente que parece que converge a 4.

Podemos intentar hacer un poco de matemáticas a ver si se puede comprobar esta convergencia. Para ello podemos expresar estos números centrales como el cociente binomial:

Podemos observar que los números centrales expresados como cociente binomial son de la forma (2n n), pues son (2 1), (4 2), (6 3) que sería el siguiente que no sale en la imagen, etc. Según esto el cociente entre números centrales sucesivos en forma de cociente binomial sería (2[n+1] n+1) / (2n n). Desarrollando con la fórmula del binomio y simplificando, este cociente puede expresarse así: (2n+2)*(2n+1)/(n+1)*(n+1). Calculando este límite cuando n tiende a infinito (puede verse el resultado en este enlace) nos da el resultado de 4.

PD.: hace algún tiempo había hecho un vídeo sobre una extensión del triángulo de Pascal a tres dimensiones