En el pasado he tocado el tema de la precisión de cálculo en Excel y los problemas que puede generar. Para evitar una ola
de pánico entre mis lectores señalemos que en la práctica estos problemas
aparecen muy raramente, pero que existen…existen.
El caso que voy a presentar está basado en una consulta de una
amiga, investigadora en el área de la biología. Supongamos esta tabla de
mediciones de lapsos
Como puede observarse, en la columna “Lapso transcurrido” se
ha calculado cuántos minutos transcurrieron entre una observación y la
anterior. Queremos controlar que no hayan transcurrido más de 15 minutos
entre cada observación, por lo que aplicamos formato condicional con la siguiente fórmula
Al hacerlo surge un problema: a pesar que todos los intervalos
son de 15 minutos, como podemos controlar a ojo desnudo, Excel marca ciertas
filas como si el intervalo hubiera excedido el tiempo permitido
El problema reside en la precisión de cálculo de Excel
(problema del floating point que no desarrollaré aquí, pero pueden consultar este artículo de la base
de conocimientos de Microsoft). Para hacer visible el problema voy a copiar los
valores de la columna C a la columna D aplicando el formato numérico General
Aparentemente todos los valores son idénticos (15/1440 =
0.10416667), pero si agregamos más decimales a la presentación en pantalla de los
números vemos lo siguiente
¡Los números, que expresan el intervalo en formato decimal,
son distintos a pesar que los intervalos son idénticos! Como Excel utiliza la
forma decimal para hacer los cálculosde tiempo y fechas, nuestro Formato Condicional falla (
Una solución posible es reducir la precisión de cálculo a 10
lugares decimales usando REDONDEAR
Otra solución, más precisa, es usar la función MINUTO para
expresar el intervalo como número entero
y en el formato condicional usar =$C4>15
Yo me encontré con este problema hace 14 años cuando desarrollamos una hoja para analizar balances y pusimos como condición que si el activo y el pasivo no eran iguales no te dejase continuar. Valla follon se monto, hasta hablamos con microsoft que nos explicaron cual era el motivo. Lastima no haber tenido esta información antes.
ResponderBorrarHola Jorge, la precisión de los decimales que representan las horas, minutos y segundos en Excel da bastantes quebraderos de cabeza.
ResponderBorrarNo soy partidario de resolver los problemas de precisión con redondeo pues este método aproximado no da resultados exactos, como bien dices, debido al número de decimales escogidos.
Por ejemplo para 15 minutos con milésimas de segundos se obtiene estos decimales:
h:mm:ss,000 10 dígitos significativos
0:14:59,999 0,0104166550925926
0:15:00,000 0,0104166666666667
0:15:00,555 0,0104230902777778
0:15:00,999 0,0104282291666803
0:15:01,000 0,0104282407407544
Con milésimas de segundo, el rango de 15 minutos se extiende desde un valor de 0,0104166666666667 hasta 0,0104282291666803 por lo que con 5 decimales bastaría para compararlos, pero ese redondeo los igualaría a 0:14:59 y 0:15:01
En el caso del artículo, redondeando a 10 decimales has conseguido igualar los lapsos de únicamente 2 valores distintos, cosa que no es general como he demostrado en la tabla de tiempos anterior.
Lo idóneo es comparar valores similares, sin redondear para no afectar la precisión de la comparación, como has explicado con la fórmula:
=MINUTO(B4-B3)
Si el lapso es de horas y minutos, se puede probar con estas fórmulas:
=--TEXTO(B4-B3,"[mm]")
=HORA(B4-B3)*60+MINUTO(B4-B3)
Si se quieren comparar segundos:
=--TEXTO(B4-B3,"[ss]")
=HORA(B4-B3)*3660+MINUTO(B4-B3)*60+SEGUNDO(B4-B3)
Espero que sirvan las soluciones propuestas que son las que uso habitualmente para comparar tiempos decimales en Excel.
Saludos.
Hola Pedro, gracias por colaborar. Muy acertadas tus observaciones.
ResponderBorrar