Suma interna de los dígitos de un número con Excel

Parece ser que la onda mística que barre el mundo en los últimos años ha llegado también a algunos de mis amigos.

A uno de ellos se le ha dado por la numerología, que según Wikipedia viene a ser algo así como

una práctica adivinatoria utilizando los números. Es un conjunto de creencias o tradiciones que pretende establecer una relación mística entre los números, los seres vivos y las fuerzas físicas o espirituales.

Mi amigo quería desarrollar un modelo en Excel que le ayudara a hacer los cálculos necesarios según la doctrina adivinatoria. Básicamente los siguientes:

• Dado que a cada letra del alfabeto le corresponde un número, debemos sumar los valores de las letras del nombre (propio y apellido por separado) y totalizarlo reduciéndolo a un número de una sola cifra
• Dada la fecha de nacimiento, sumar los dígitos que la componen y totalizarlos tal como hicimos con el nombre.

Puesto en términos de Excel si a la letra A le corresponde el número 1 y a la letra N el 14, el nombre Ana equivale a 1 +14 = 15 y a su vez 1 + 5 = 6.

El desafío es hacerlo sin macros, por supuesto. Para realizar la suma interna de los dígitos de un número podemos usar esta fórmula

=SUMAPRODUCTO(EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1)*1)

Vamos a explicar esta fórmula por pasos:

=FILA(INDIRECTO("1:" & LARGO(A2))) crea una vector vertical con una serie de número que empieza en 1 y termina en el número que coincide con el largo del número cuyos dígitos queremos sumar. En nuestro ejemplo el resultado es FILA(1:5) es decir el vector {1,2,3,4,5}

EXTRAE(A2,FILA(INDIRECTO("1:" & LARGO(A2))),1) equivale a EXTRAE(A2, {1,2,3,4,5},1), es decir, extraemos el primer dígito, luego el segundo y así sucesivamente hasta el quinto. Recordemos que SUMAPRODUCTO es una función matricial.

Finalmente SUMAPRODUCTO multiplica cada miembro del vector creado con EXTRAE por 1 y los suma, dando el resultado, 15. Esta multiplicación es necesaria ya que la función EXTRAE da como resultado textos, no valores numéricos.

Para reducir el resultado de dos dígitos a una única cifra tenemos que volver a aplicar la fórmula. Esto puede hacerse en otra celda

o anidando las funciones

Si el resultado del primer cálculo diera un número de tres cifras, la fórmula anidada sería demasiado larga. Por lo tanto dividir el cálculo en varias celdas parece ser la mejor solución.

Actualización: una solución más eficiente puede leerse en esta nota.

Para aplicar esta fórmula a letras, debemos primero convertirlas a sus equivalentes numéricos. Suponiendo que el número equivalente sea el número de orden en el alfabeto, podemos construir esta lista

Incluir el rango en un nombre ("alfabeto") y usar BUSCARV para calcular el valor de cada letra.

Por comodidad y para no complicar las fórmulas vamos a dividir el nombre en varias celdas poniendo cada letra en celda contiguas. Creamos una tabla de dos columnas por 20 filas; en la primer columna ponemos las letras del nombre y en la segunda columna calculamos el valor numérico de la letra con BUSCARV

Para ocultar los resultados #N/A y los bordes donde no hay letras usamos formato condicional

Para sumar los valores de cada letra usamos la fórmula matricial

={SUMA(SI(ESERROR(C2:C21),0,C2:C21))}

Y en las celdas G2 y F2 usamos las fórmulas con la función SUMAPRODUCTO que mostramos más arriba.

Finalmente podemos ocultar las columnas G y H y mostrar el resultado final

Así nos enteramos que Jorge vale 3, sobre cuyo significado no tengo la más pálida idea.