Hay un error de cálculo feo que ha estado apareciendo en Excel. Parece que el problema se adentra en el motor de cálculo de Excel y no será fácil de solucionar.
En el centro del problema hay un hecho simple: Excel almacena 15 dígitos de precisión en una celda. Puede tener números de 20 dígitos, pero cualquier dígito entre el último dígito significativo y el lugar decimal debe ser cero.
Recientemente, he visto dos casos en los que el motor de cálculo de Excel arrojaba resultados incorrectos. Cuando profundicé en el problema y miré el XML subyacente, me sorprendió ver que Excel almacenaba secretamente 17 dígitos en el XML.
El problema es que Excel solo mostrará 15 dígitos. Entonces, cree que tiene un número almacenado como 0.123456789012345 pero en realidad está almacenado como 0.12345678901234567.
No puede ver esos dos últimos dígitos. Y la mayoría de las funciones de Excel ignoran esos dos últimos dígitos. Si * todas * las funciones ignoraran los dos últimos dígitos, no tendríamos ningún problema. Pero hasta ahora, la clasificación, el RANGO y la FRECUENCIA utilizan los 17 dígitos.
A continuación se muestra un truco muy conocido para clasificar las celdas. Si necesita que cada rango aparezca exactamente una vez, puede combinar RANK y COUNTIF. En la imagen de abajo, Claire, Flo, Ivana y Lucy están empatadas al 115%. Usando la fórmula RANK + COUNTIF, deben clasificarse como 5, 6, 7 y 8.
Pero la fórmula falla. Dos filas se clasifican como 7. Eso nunca sucede. Cuatro fórmulas en la columna D aseguran que el 115% en B6, B9, B12 y B15 sean iguales. La =B6=B15fórmula informa que ambas celdas contienen los mismos datos.
Mientras trataba de aislar el problema, mire solo la función RANK. Debería reportar un empate de 4 vías en el cuarto lugar para las personas con 115%. Pero de alguna manera, Lucy en la Fila 15 está clasificada por delante de las otras tres.
Para resolverlo, envié una solicitud de ayuda a los otros MVP de Excel. Jan Karel Pieterse abrió el archivo de Excel y miró en el XML. En el XML, puede ver que están almacenando 17 dígitos de precisión. Las cuatro celdas que parecen un vínculo en Excel no están vinculadas en el XML. Uno del 115% se almacena como 1,1500000000000001 y los otros son 1,1499999999999999.
Hasta ahora, la clasificación, la clasificación y la función FRECUENCIA utilizan los dígitos adicionales. ¿Por que eso es un problema? Porque contamos con RANK y COUNTIF para que ambos usen el mismo número de dígitos. Con una función que usa 15 dígitos y la otra que usa 17 dígitos, tiene un problema.
Por ahora, la solución parece convertir todas sus respuestas usando =ROUND(A4,15).
Todos los viernes, examino un error u otro comportamiento sospechoso en Excel. Este error de cálculo es difícil de detectar y se considera un pez gordo.
Pensamiento del día de Excel
Les he pedido a mis amigos de Excel Master sus consejos sobre Excel. Pensamiento de hoy para reflexionar:
"Cada vez que fusionas células asesinas a un gatito"
Szilvia Juhasz
