lunes, abril 11, 2016

Formato condicional y precisión de cálculo de Excel

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 ( los marcados con la X, son un poco más grandes y por lo tanto son señalados por el formato condicional).

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


3 comentarios:

  1. 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.

    ResponderBorrar
  2. Hola Jorge, la precisión de los decimales que representan las horas, minutos y segundos en Excel da bastantes quebraderos de cabeza.

    No 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.

    ResponderBorrar
  3. Hola Pedro, gracias por colaborar. Muy acertadas tus observaciones.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.