Fórmula genérica
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Resumo
Para contar valores que estão fora da tolerância em um conjunto de dados, você pode usar uma fórmula baseada nas funções SUMPRODUCT e ABS. No exemplo mostrado, a fórmula em F6 é:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
onde "dados" é o intervalo nomeado B5: B14, "destino" é o intervalo nomeado F4 e "tolerância" é o intervalo nomeado F5.
Explicação
Esta fórmula conta quantos valores não estão no intervalo de uma tolerância fixa. A variação de cada valor é calculada com isto:
ABS(data-target)
Como o intervalo nomeado "dados" contém 10 valores, subtrair o valor alvo em F4 criará uma matriz com 10 resultados:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
A função ABS altera quaisquer valores negativos para positivos:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Esta matriz é comparada à tolerância fixa em F5:
ABS(data-target)>tolerance
O resultado é uma matriz ou valores TRUE FALSE, e o duplo negativo os altera para uns e zeros. Dentro de SUMPRODUCT, a matriz final se parece com isto:
(0;0;1;0;1;0;0;1;0;1)
onde zeros representam valores dentro da tolerância e 1s representam valores fora da tolerância. SUMPRODUCT soma os itens da matriz e retorna o resultado final, 4.
Todos os valores dentro da tolerância
Para retornar "Sim" se todos os valores em um intervalo de dados estiverem dentro de uma determinada tolerância, e "Não" se não, você pode adaptar a fórmula assim:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Se SUMPRODUCT retornar qualquer número maior que zero, IF avaliará o teste lógico como TRUE. Um resultado zero será avaliado como FALSO.
Destacar valores fora da tolerância
Você pode destacar valores fora da tolerância com uma regra de formatação condicional baseada em uma fórmula como esta:
=ABS(B5-target)>tolerance
Esta página lista mais exemplos de formatação condicional com fórmulas.
