Fórmula genérica
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Resumen
Para contar valores que están fuera de tolerancia en un conjunto de datos, puede usar una fórmula basada en las funciones SUMPRODUCT y ABS. En el ejemplo que se muestra, la fórmula en F6 es:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
donde "datos" es el rango con nombre B5: B14, "objetivo" es el rango con nombre F4 y "tolerancia" es el rango con nombre F5.
Explicación
Esta fórmula cuenta cuántos valores no están dentro del rango de una tolerancia fija. La variación de cada valor se calcula con esto:
ABS(data-target)
Debido a que el rango con nombre "datos" contiene 10 valores, restar el valor objetivo en F4 creará una matriz con 10 resultados:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
La función ABS cambia cualquier valor negativo a positivo:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Esta matriz se compara con la tolerancia fija en F5:
ABS(data-target)>tolerance
El resultado es una matriz o valores VERDADERO FALSO, y el doble negativo los cambia a unos y ceros. Dentro de SUMPRODUCT, la matriz final se ve así:
(0;0;1;0;1;0;0;1;0;1)
donde los ceros representan valores dentro de la tolerancia y los 1 representan valores fuera de tolerancia. SUMPRODUCT luego suma los elementos de la matriz y devuelve un resultado final, 4.
Todos los valores dentro de la tolerancia
Para devolver "Sí" si todos los valores en un rango de datos están dentro de una tolerancia dada, y "No" si no, puede adaptar la fórmula de esta manera:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Si SUMPRODUCT devuelve cualquier número mayor que cero, IF evaluará la prueba lógica como VERDADERA. Un resultado cero se evaluará como FALSO.
Resaltar valores fuera de tolerancia
Puede resaltar valores fuera de tolerancia con una regla de formato condicional basada en una fórmula como esta:
=ABS(B5-target)>tolerance
Esta página enumera más ejemplos de formato condicional con fórmulas.
