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

Nota

Office 365 ProPlus pasa a llamarse Microsoft 365 Apps para empresas. Para obtener más información sobre este cambio, lea esta publicación de blog.

Resumen

En este artículo se describe cómo Microsoft Excel almacena y calcula 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 del equipo. La especificación 754 es una especificación muy 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,1. Sin embargo, el mismo número en formato binario se convierte en el siguiente decimal binario repetido:

0001100111111001110011 (y así sucesivamente)

Esto se puede repetir infinitamente. 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, se sigue que 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

  • Underflow: Underflow 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 precisión se perderá. Los números del extremo pequeño 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 número 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.

  • Infinites positivos/negativos: las infiniciones se producen cuando se divide entre 0. Excel no admite infinities, sino que proporciona un #DIV/0. error en estos casos.

  • Not-a-Number (NaN): NaN se usa para representar operaciones no válidas (como el infinito/infinito, el infinito infinito o la raíz cuadrada de -1). Los naN permiten que un programa continúe pasando una operación no válida. En su lugar, Excel 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 El exponente La mantisa
1 bit de signo Exponente de 11 bits 1 bit implícito + fracción de 52 bits

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 +1.023 y -1.022) 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 independientes. 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 - bias)

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 se obtiene un poco 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 1s y 0s.

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

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 que la celda A1. De hecho, si compara las celdas A1 y C1 con la función IF, por ejemplo IF(A1=C1), el resultado será TRUE. 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.

Corregir errores de precisión

Excel ofrece dos métodos básicos para compensar los errores de redondeo: la función ROUND y la opción Precision as displayed o Set precision as displayed workbook.

Método 1: la función ROUND

Con los datos anteriores, en el ejemplo siguiente se usa la función ROUND para forzar 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: =IF(C1=1.2E+200, TRUE, FALSE)

Esto da como resultado el valor TRUE.

Método 2: Precisión tal como se muestra

En algunos casos, puede evitar que los errores de redondeo afecten al trabajo mediante la opción Precisión como se muestra. 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, a continuación, haga clic en la categoría Avanzadas.
  2. En la sección Al calcular este libro, seleccione el libro que desee y, a continuación, active la casilla Establecer precisión como mostrada.

Por ejemplo, si elige un formato de número que muestra dos posiciones decimales y, a continuación, activa la opción Precisión como mostrada, 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 habilitar esta opción.

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:

000110011111001110011 (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 exactamente 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).

Por ejemplo, considere el siguiente ejemplo sencillo en 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,99999999999996 como salida. El pequeño error al representar 0,0001 en binario se propaga a la suma.

Ejemplo: agregar 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 secundario en la celda A1 y, a continuación, haga clic en Formato de celdas. En la ficha Número, haga clic en Científico en Categoría. Establece 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 temporalmente 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 secundario en la celda A1 y, a continuación, haga clic en Formato de celdas. En la ficha Número, haga clic en Científico en Categoría. Establece las posiciones decimales en 15.

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

Sin embargo, Excel 97 introdujo una optimización que intenta corregir este problema. Si una operación de suma o resta resulta en un valor en o muy cercano a cero, Excel 97 y versiones posteriores compensarán cualquier error introducido como resultado de la conversión de un operando a y desde binario. El ejemplo anterior cuando se realiza en Excel 97 y versiones posteriores muestra correctamente 0 o 0,00000000000000E+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 de World Wide Web: