Explicación de el error de Excel 2007

Me imagino que muchos de ustedes ya sabran del reciente error descubierto en Excel 2007, en el cual, al intentar hacer la operacion: 77,1*850 muestra 100.000 en vez de 65.535 que es el resultado correcto.

Para tratar de explicar un poco como se produce el error, primero que todo, debemos tener en cuenta que Excel guarda los numeros internamente en formato binario, pero que estos son mostrados en las celdas como una cadena de caracteres (String). Para este ejemplo, cuando escribimos en una celda de Excel el número 77,1 éste lo guarda internamente usando 64 bits como:

0100 0000 0101 0011 0100 0110 0110 0110
0110 0110 0110 0110 0110 0110 0110 0110

En la celda se muestran cuatro caracteres: “7″, “7″, “.” y “1″

Luego entonces, el error se encuentra en alguna parte de la función que convierte los numeros binarios a cadenas de caracteres. Esta función tiene un error tal que causa que algunos numeros que se encuentran muy cercanos a 65.535 sean incorrectamente mostrados como 100.000.

Sin embargo, el error es solo en la forma como se muestra el numero en la celda, porque internamente el resultado de la operacion es correcto, es decir que el numero guardado internamente por excel es 65.535 y no 100.000, veamos:

A1 = 77.1*850 -> 100.000
A2 = A1-1 -> 65.534

Sin embargo, parece ser que el error no está solo con numero cercanos a 65.535 sino tambien con numero cercanos a 65.536:

A3 = A1+1 -> 100.001

Ahora bien, he mencionado que el error se presenta con numero cercanos 65.535 y 65.536 pero no con 65.535 y 65.536 exactamente, esto lo podemos comprobar si ingresamos en una celda estos números y no obtenemos ningún error. Si observamos de nuevo la representación binaria de 77.1 vemos como al final hay una repeticion de 0110 0110 0110 … esto se debe a que el 0.1 no tiene una representación exacta en binario, por lo que se escribe como una repeticion de numeros. Es algo asi como representar 1/3 en decimal, que es 0.33333333… . Si en decimal, trataramos de hacer 1/3*3 no obtendriamos exactamente 1 sino 0.9999999, dependiendo de cuantas cifras después del punto tomamos para hacer la operación. Lo que pasa en Excel es algo similar, al tratar de hacer la operación 77.1*850 se obtiene un número muy cercano a 65.535 pero en numero binario, que al ser transformado a cadena de caracteres arroja 100.000.

Según el grupo de desarrolladores de Excel, están trabajando en la solucion de este error que se supone no deberia ser tan grave.

Fuentes:

http://www.joelonsoftware.com/items/2007/09/26b.html

http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx