martes, 19 de julio de 2016

Parte decimal de un número con Excel

Analizaremos hoy una curiosidad en cuanto a la precisión de Excel al operar con ciertos números decimales.
Se trata de dar solución a un usuario del blog:
[...] necesito que en una celda se puedan poner números con un solo decimal, he logrado poner la formula de validación pero cuando pongo por ej XX,0 me da error en cambio si pongo xx,1 funciona, osea puedo poner cualquier numero menos algún número que sea ,0 o entero[...]


Sin duda los primeros intentos podrían ser empleando las funciones ENTERO, TRUNCAR o incluso RESIDUO (todas válidas en teoría).
Fijémonos en los siguientes ejemplos:
=B2-TRUNCAR(B2;0)
=B2-ENTERO(B2)-1
=RESIDUO(B2;1)-1

Parte decimal de un número con Excel



A priori estas formas deberían devolvernos la parte decimal, sin problemas... en nuestro ejemplo 0,89
Sin embargo vemos como a partir de la posición decimal 15, aparece un valor!!!, haciendo nuestro intento vano.

Se trata del error conocido como de 'punto flotante'.
En realidad no es un error de Excel; si no que se produce porque el estándar de 'punto flotante' 754 Institute of Electrical and Electronics Engineers (IEEE) requiere que los números se almacenan en formato binario.


Antes de contar soluciones posibles, veremos qué función es la que nos permitirá dar solución directa:
=SI.ERROR(VALOR(EXTRAE(B2;ENCONTRAR(",";B2)+1;LARGO(B2)));0)<10

Parte decimal de un número con Excel


Básicamente esta función trata el número como un texto, extrayendo la parte a la derecha de la coma, i.e., la parte decimal.
Una vez separada como texto lo convertimos en valor numérico de nuevo, y para dar respuesta a nuestro lector, evaluamos si el número es inferior a 10, lo que nos asegura que la parte decimal tiene un único decimal.


Para completar el ejercicio, seleccionamos la celda B2 a validar, accedemos a la herramienta Validación de datos > Personalizado y en el campo Fórmula añadimos:
=SI.ERROR(VALOR(EXTRAE(B2;ENCONTRAR(",";B2)+1;LARGO(B2)));0)<10 es decir, nuestra fórmula comentada.

Parte decimal de un número con Excel



Ampliaré el post de hoy con otras posibles soluciones...
Una solución simple es, conociendo la posición 15 de ese 'punto flotante', emplear la función REDONDEAR(VALOR;14).
Por ejemplo:
=REDONDEAR(B2-TRUNCAR(B2;0);14)
=REDONDEAR(B2-ENTERO(B2)-1;14)
=REDONDEAR(RESIDUO(B2;1)-1;14)


Otra solución más elaborada, y menos práctica, es emplear la opción avanzada: Establecer precisión de pantalla, con la que indicamos a Excel que opere con los decimales/datos visibles según formato en ese momento en pantalla !!!
(OJO con esta forma de actuar que podría tener efectos secundarios en nuestras operaciones).

Parte decimal de un número con Excel


En el paso siguiente bastará aplicar un formato de número con un número decimales bastante para evitar la aparición indeseable del 'punto flotante'.
Cualquier operación sobre nuestras celdas tomará la precisión visible en ese momento...

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.