Formato indeseado de número en Excel

Cuando introducimos un valor en una celda, Excel intenta determinar el tipo de valor y darle el formato adecuado. Por ejemplo, si escribimos 1/2 en una celda Excel lo transforma al primero de febrero del año corriente



Lo mismo sucede si el valor contiene un guión.

Si el número (de hecho, un conjunto de cifras) contiene dos puntos o las letras A o P antecedidas por un espacio, el valor es transformado en hora



Si ingresamos un número que contiene ceros iniciales, Excel elimina los ceros y convierte el valor en número. Además y como ya hemos visto, si el número excede las 12 cifras Excel aplica el formato científico y si se exceden las 15, las cifras supernumerarias son transformadas en ceros. Esto último lo he vivido en carne propia hace varios años atrás cuando tuvimos que ingresar números de tarjetas de crédito (que tienen 16 cifras).

El remedio a este tipo de situaciones es darle el formato adecuado al rango de celdas que vamos a usar, evitando así que Excel decida por su cuenta. Existen también algunos trucos de teclado, en caso que queramos ahorrarnos el proceso de dar formato a las celdas.

# Si queremos ingresar números con ceros iniciales o códigos de tarjetas de crédito debemos darle al rango el formato Texto. Otra alternativa es poner un apóstrofe al comienzo (por ejemplo, '00001509).

# Si queremos ingresar fracciones debemos dar el formato Fracción o escribir la parte entera de la fracción, dejar un espacio y escribir el resto de la expresión. Por ejemplo, para escribir 2 1/3, escribimos 2, apretamos la barra de espacios e inmediatamente 2/3



Nótese que en la barra de las fórmulas el valor aparece como 2,3333333333

Si queremos ingresar una fracción de tipo x/y, por ejemplo 3/4 escribimos en la celda 0 3/4




# Si queremos ingresar una expresión que indica relación entre dos valores, por ejemplo 5:6, debemos formar la celda como Texto o anteceder la expresión con un espacio (o apóstrofe).

Números grandes en Excel – segunda nota

Excel guarda los números con un límite de precisión de 15 cifras. Esto se hace evidente cuando introducimos un número de más de 15 cifras. Veamos qué pasa cuando introducimos el número 123456789123456789 (18 cifras) en una celda a la cual le hemos de antemano el formato de número para evita que Excel ponga el formato científico automáticamente




Al apretar Enter todas las cifras a la derecha de la posición 15 son convertidas en ceros.
El mismo problema existe cuando la parte fraccional de un número supera las 15 cifras.


Por lo general esta limitación no es significativa, en especial si usamos Excel para cálculos financieros. Si necesitamos operar con mayor precisión de la que permite Excel dos posibilidades son:


# - descargar el complemento xlPrecision


# - utilizar los conceptos esbozados por Tushar Metha en esta nota



La ventaja del complemento xlPrecision es que también soluciona el problema precisión y redondeo que existe en Excel.



Es este el problema de la conversión binaria (no sólo de Excel) que se hace evidente con este ejemplo: restamos 4.0 de 4.1

Aparentemente el resulta es el correcto: 0.1. Veamos qué pasa cuando formamos la celda para que muestre más de 15 decimales

El resultado no es exactamente 0.1.

Technorati Tags: MS Excel

Números grandes en Excel – primera nota

Un lector me consulta cómo hacer en Excel para que cuando introducimos un número grande éste no se convertido en forma automática a la notación científica.

Por ejemplo, si introducimos el número 123456789123 en una celda, lo que veremos en pantalla es 1.23457E+11

Esta consulta involucra en realidad dos temas:

# formato automático de números en Excel (cómo exhibe Excel los números en la pantalla)

# cálculos con números de más de 15 cifras significativas.

En esta nota tocaremos el primer tema, y dejaremos el segundo para una futura nota.

Empecemos por ver un ejemplo donde nuestro número tiene por lo menos 12 cifras, pero menos de 15.

Volviendo a nuestro ejemplo (que tiene 12 cifras) veamos qué significa 1.23568E+11.

La notación E+11 es una forma abreviada de escribir el número. Esta notación nos dice que hemos tomado el número y lo hemos divido por 10 elevado la potencia de 11 (es decir 100,000,000,000).

Cuando se trata de números grandes, Excel pone el formato científico automáticamente si la celda está formada como General (el formato por defecto de Excel).

Si queremos evitar que Excel ponga el formato científico tenemos que dar un formato de número a la celda antes de ingresar el número. Si el número tiene menos de 15 cifras, podemos cambiar el formato después de haber introducido el número.

En esta imagen podemos ver que si bien los números en la columna A aparecen en notación científica, Excel los “ve” tal como son; en la columna B vemos los mismos números introducidos en celdas previamente formadas con formato numérico

Ahora veamos una “curiosidad” de Excel cuando se trata de números grandes



En las celdas A1:A2 hemos puesto el número 123456789123 que Excel exhibe como 1,23457E+11. En la barra de fórmulas podemos ver que sólo se trata de exhibición; Excel sigue “viendo” el número tal como la introducimos en la celda. En el rango B1:B2 ponemos el mismo número pero previamente hemos formado las celdas como Número.

Ahora usaremos el icono de aumentar decimales para agregar un decimal en las celdas A1 y A2

Como puede observarse, se ha producido una pérdida de precisión. Ahora el número en las celdas, tanto en el rango A1:A2 como en B1:B2, es 123456800000!

Hasta aquí hemos usado un número de menos de 15 cifras. ¿Qué paso cuando usamos un número de más de 15 cifras?

En la celda A1 introducimos el número 123456789123456789 (18 cifras)

Sin embargo, el número que aparece en Excel después de apretar Enter es 123456789123456000

Es decir, las últimas tres cifras han sido reemplazadas por ceros!. Toda cifra después de la posición 15, será convertida a cero. Esto se debe a una limitación, no solamente de Excel, que será explicada en la próxima nota sobre el tema.

Lo que debemos saber a esta altura de los acontecimientos es que:

# Excel tiene una limitación de precisión si el número excede las 15 cifras significativas

# para introducir números de más de 15 cifras debemos convertirlos en texto, ya sea anteponiendo un ' o dando el formato Texto a la celda antes de introducir el número.

Esto genera un serio problema si tenemos que realizar cálculos con números grandes. Como enfrentarnos a estos problemas será el tema de la próxima nota.

Technorati Tags: MS Excel