La aritmética de punto flotante puede dar resultados inexactos en Excel

Resumen

En este artículo se describe cómo Microsoft Excel almacena y calcula los números de punto flotante. Esto puede afectar a los resultados de algunos números o fórmulas debido al redondeo o al truncamiento de datos.

Información general

Microsoft Excel se diseñó en torno a la especificación IEEE 754 para determinar cómo almacena y calcula los números de punto flotante. IEEE es el Instituto de Ingenieros Eléctricos y Electrónicos, un organismo internacional que, entre otras cosas, determina los estándares de software y hardware de los equipos. La especificación 754 es una especificación ampliamente adoptada que describe cómo deben almacenarse los números de punto flotante en un equipo binario. Es popular porque permite que los números de punto flotante se almacenen en una cantidad razonable de espacio y los cálculos se produzcan con relativa rapidez. El estándar 754 se usa en las unidades de punto flotante y procesadores de datos numéricos de casi todos los microprocesadores basados en PC actuales que implementan matemáticas de punto flotante, incluidos los procesadores Intel, Motorola, Sun y MIPS.

Cuando se almacenan números, un número binario correspondiente puede representar cada número o número fraccionamiento. Por ejemplo, la fracción 1/10 se puede representar en un sistema de números decimales como 0 y 1. Sin embargo, el mismo número en formato binario se convierte en el siguiente decimal binario repetido:

0001100110011100110011 (y así sucesivamente)

Esto se puede repetir de forma infinita. Este número no se puede representar en una cantidad finita (limitada) de espacio. Por lo tanto, este número se redondea hacia abajo aproximadamente -2,8E-17 cuando se almacena.

Sin embargo, hay algunas limitaciones de la especificación IEEE 754 que se encuentran en tres categorías generales:

• Limitaciones máximas/mínimas
• Precisión
• Repetición de números binarios

Más información

Limitaciones máximas/mínimas

Todos los equipos tienen un número máximo y un número mínimo que se pueden controlar. Dado que el número de bits de memoria en los que se almacena el número es finito, el número máximo o mínimo que se puede almacenar también es finito. Para Excel, el número máximo que se puede almacenar es 1,79769313486232E+308 y el número positivo mínimo que se puede almacenar es 2,2250738585072E-308.

Casos en los que nos adherimos a IEEE 754

• Subdesbordamiento: el subdesbordamiento se produce cuando se genera un número que es demasiado pequeño para representarse. En IEEE y Excel, el resultado es 0 (con la excepción de que IEEE tiene un concepto de -0 y Excel no).
• Desbordamiento: el desbordamiento se produce cuando un número es demasiado grande para representarse. Excel usa su propia representación especial para este caso (#NUM!).

Casos en los que no nos adherimos a IEEE 754

• Números desnormalizados: un número desnormalizado se indica mediante un exponente de 0. En ese caso, el número completo se almacena en la mantisa y la mantisa no tiene un 1 inicial implícito. Como resultado, se pierde precisión y, cuanto menor sea el número, más se perderá. Los números del extremo inferior de este rango solo tienen un dígito de precisión.

  Ejemplo: un número normalizado tiene un 1 inicial implícito. Por ejemplo, si la mantisa representa 0011001, el número normalizado se convierte en 10011001 debido al 1 inicial implícito. Un número desnormalizado no tiene un número inicial implícito, por lo que en nuestro ejemplo de 0011001, el número desnormalizado sigue siendo el mismo. En este caso, el número normalizado tiene ocho dígitos significativos (10011001), mientras que el desnormalizado tiene cinco dígitos significativos (11001) y los ceros iniciales son insignificantes.

  Los números no normalizados son básicamente una solución alternativa para permitir que se almacenen números menores que el límite inferior normal. Microsoft no implementa esta parte opcional de la especificación porque los números desnormalizados, por su propia naturaleza, tienen un número variable de dígitos significativos. Esto puede permitir que se introduzca un error significativo en los cálculos.

• Infinitos positivos/negativos: los infinitos se producen cuando se divide entre 0. Excel no admite infinitos, sino que muestra un error #DIV/0! en estos casos.

• No es un número (NaN): NaN se usa para representar operaciones no válidas (como infinito/infinito, infinito-infinito o la raíz cuadrada de -1). Los NaN permiten que un programa continúe después de una operación no válida. Excel, en su lugar, genera inmediatamente un error como #NUM! o #DIV/0!.

Precisión

Un número de punto flotante se almacena en binario en tres partes dentro de un intervalo de 65 bits: el signo, el exponente y la mantisa.

El signo almacena el signo del número (positivo o negativo), el exponente almacena la potencia de 2 a la que se eleva o baja el número (la potencia máxima/mínima de 2 es +1023 y -1022) y la mantisa almacena el número real. El área de almacenamiento finita de la mantisa limita la proximidad de dos números de punto flotante adyacentes (es decir, la precisión).

La mantisa y el exponente se almacenan como componentes separados. Como resultado, la cantidad de precisión posible puede variar según el tamaño del número (la mantisa) que se manipule. En el caso de Excel, aunque Excel puede almacenar números de 1,79769313486232E308 a 2,2250738585072E-308, solo puede hacerlo dentro de 15 dígitos de precisión. Esta limitación es un resultado directo de seguir estrictamente la especificación IEEE 754 y no es una limitación de Excel. Este nivel de precisión también se encuentra en otros programas de hoja de cálculo.

Los números de punto flotante se representan en la siguiente forma, donde exponente es el exponente binario:

X = Fracción * 2^(exponente - sesgo)

La fracción es la parte fraccional normalizada del número, normalizada porque el exponente se ajusta de modo que el bit inicial siempre sea un 1. De esta forma, no tiene que almacenarse y obtiene un bit más de precisión. Por este motivo, hay un bit implícito. Esto es similar a la notación científica, donde se manipula el exponente para que tenga un dígito a la izquierda del punto decimal; excepto en binario, siempre se puede manipular el exponente para que el primer bit sea un 1, ya que solo hay 1 y 0.

Sesgo es el valor de sesgo usado para evitar tener que almacenar exponentes negativos. El sesgo de los números de precisión sencilla es 127 y 1023 (decimal), el de números de doble precisión. Excel almacena los números con doble precisión.

Ejemplo de uso de números muy grandes

Escriba lo siguiente en un libro nuevo:

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1 

El valor resultante de la celda C1 sería 1,2E+200, el mismo valor de la celda A1. De hecho, si compara las celdas A1 y C1 con la función SI, por ejemplo SI(A1=C1), el resultado será VERDADERO. Esto se debe a la especificación IEEE de almacenar solo 15 dígitos significativos de precisión. Para poder almacenar el cálculo anterior, Excel requeriría al menos 100 dígitos de precisión.

Ejemplo de uso de números muy pequeños

Escriba lo siguiente en un libro nuevo:

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

El valor resultante en la celda C1 sería 1,00012345678901 en lugar de 1,000123456789012345. Esto se debe a la especificación IEEE de almacenar solo 15 dígitos significativos de precisión. Para poder almacenar el cálculo anterior, Excel requeriría al menos 19 dígitos de precisión.

Corrección de errores de precisión

Excel ofrece dos métodos básicos para compensar los errores de redondeo: la función REDONDEAR y la opción de libro Precisión de pantalla o Establecer precisión de pantalla.

Método 1: la función REDONDEAR

Con los datos anteriores, en el ejemplo siguiente se usa la función REDONDEAR para ajustar un número a cinco dígitos. Esto le permite comparar correctamente el resultado con otro valor.

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5) 

Esto da como resultado 1,2E+200.

D1: =SI(C1=1,2E+200, VERDADERO, FALSO)

Esto da como resultado el valor VERDADERO.

Método 2: Precisión de pantalla

En algunos casos, puede evitar que los errores de redondeo afecten al trabajo mediante la opción Precisión de pantalla. Esta opción fuerza que el valor de cada número de la hoja de cálculo sea el valor mostrado. Para activar esta opción, siga estos pasos.

1. En el menú Archivo, haga clic en Opciones y, después, en la categoría Avanzados.
2. En la sección When calculating this workbook (Al calcular este libro), seleccione el libro que desee y, a continuación, la casilla Establecer precisión de pantalla.

Por ejemplo, si elige un formato de número que muestra dos posiciones decimales y, a continuación, activa la opción Precisión de pantalla, se pierde toda precisión más allá de dos posiciones decimales al guardar el libro. Esta opción afecta al libro activo, incluidas todas las hojas de cálculo. No puede deshacer esta opción y recuperar los datos perdidos. Se recomienda guardar el libro antes de habilitarla.

Repetición de números binarios y cálculos con resultados cercanos a cero

Otro problema confuso que afecta al almacenamiento de números de punto flotante en formato binario es que algunos números finitos que no se repiten en la base decimal 10 son números infinitos que se repiten en binario. El ejemplo más común de esto es el valor 0,1 y sus variaciones. Aunque estos números se pueden representar perfectamente en la base 10, el mismo número en formato binario se convierte en el siguiente número binario repetido cuando se almacena en la mantisa:

000110011001100110011 (y así sucesivamente)

La especificación IEEE 754 no ofrece ninguna asignación especial para ningún número. Almacena lo que puede en la mantisa y trunca el resto. Esto produce un error de aproximadamente -2,8E-17 o 0,000000000000000028 cuando se almacena.

Incluso las fracciones decimales comunes, como el decimal 0,0001, no se pueden representar de forma exacta en binario. (0,0001 es una fracción binaria repetida que tiene un período de 104 bits). Esto es similar a por qué la fracción 1/3 no puede representarse exactamente en decimales (un 0,3333333333333333333333333333 que se repite).

Por ejemplo, considere el siguiente ejemplo simple de Microsoft Visual Basic para Aplicaciones:

   Sub Main()
      MySum = 0
      For I% = 1 To 10000
         MySum = MySum + 0.0001
      Next I%
      Debug.Print MySum
   End Sub

Esto IMPRIMIRÁ 0,999999999999996 como resultado. El pequeño error al representar 0,0001 en binario se propaga a la suma.

Ejemplo: adición de un número negativo

1. Escriba lo siguiente en un libro nuevo:

   A1: =(43,1-43,2)+1

2. Haga clic con el botón derecho en la celda A1 y luego haga clic en Formato de celdas. En la pestaña Número, haga clic en Científica en Categoría. Establezca las Posiciones decimales en 15.

En lugar de mostrar 0,9, Excel muestra 0,89999999999999999. Dado que (43,1-43,2) se calcula primero, -0,1 se almacena de forma temporal y el error de almacenar -0,1 se introduce en el cálculo.

Ejemplo cuando un valor alcanza cero

1. En Excel 95 o versiones anteriores, escriba lo siguiente en un nuevo libro:

   A1: =1,333+1,225-1,333-1,225

2. Haga clic con el botón derecho en la celda A1 y luego haga clic en Formato de celdas. En la pestaña Número, haga clic en Científica en Categoría. Establezca las Posiciones decimales en 15.

En lugar de mostrar 0, Excel 95 muestra -2,22044604925031E-16.

Excel 97, sin embargo, introdujo una optimización que intenta corregir este problema. Si una operación de suma o resta resulta en un valor de cero o muy cercano, Excel 97 y versiones posteriores compensarán cualquier error introducido como resultado de convertir un operando a y desde binario. El ejemplo anterior cuando se realiza en Excel 97 y versiones posteriores muestra correctamente 0 o 0,000000000000000E+00 en la notación científica.

Para obtener más información acerca de los números de punto flotante y la especificación IEEE 754, consulte los siguientes sitios web:

• https://www.ieee.org
• https://steve.hollasch.net/cgindex/coding/ieeefloat.html