Há um erro de cálculo feio que está aparecendo no Excel. Parece que o problema é profundo no mecanismo de cálculo do Excel e não será fácil de corrigir.
No cerne do problema está um fato simples: o Excel armazena 15 dígitos de precisão em uma célula. Você pode ter números com 20 dígitos, mas todos os dígitos entre o último dígito significativo e a casa decimal devem ser zero.
Eu vi dois casos recentemente em que o mecanismo de cálculo do Excel estava retornando resultados errados. Quando investiguei o problema e examinei o XML subjacente, fiquei surpreso ao ver que o Excel estava armazenando secretamente 17 dígitos no XML.
O problema é que o Excel exibirá apenas 15 dígitos. Então você acha que tem um número armazenado como 0,123456789012345, mas na verdade está armazenado como 0,12345678901234567.
Você não pode ver esses dois últimos dígitos. E a maioria das funções do Excel estão ignorando esses dois últimos dígitos. Se * todas * as funções estivessem ignorando os dois últimos dígitos, não teríamos problemas. Mas até agora, sorting, RANK e FREQUENCY estão usando todos os 17 dígitos.
Abaixo está um truque conhecido para classificar células. Se você precisar que cada classificação apareça exatamente uma vez, você pode combinar RANK e COUNTIF. Na imagem abaixo, Claire, Flo, Ivana e Lucy estão empatadas em 115%. Usando a fórmula RANK + COUNTIF, eles devem ser classificados em 5, 6, 7 e 8.
Mas a fórmula falha. Duas linhas são classificadas como 7. Isso nunca acontece. Quatro fórmulas na coluna D garantem que 115% em B6, B9, B12 e B15 sejam iguais. A =B6=B15fórmula informa que ambas as células contêm os mesmos dados.
Como tentei isolar o problema, olhe apenas para a função RANK. Deve relatar um empate a quatro na 4ª posição para as pessoas com 115%. Mas de alguma forma, Lucy na linha 15 está classificada à frente das outras três.
Para descobrir isso, enviei um pedido de ajuda aos outros MVP do Excel. Jan Karel Pieterse abriu o arquivo Excel e olhou no XML. No XML, você pode ver que eles estão armazenando 17 dígitos de precisão. As quatro células que parecem empatadas no Excel não são vinculadas no XML. Um dos 115% é armazenado como 1,1500000000000001 e os outros são 1,1499999999999999.
Até agora, a classificação, a classificação e a função FREQUÊNCIA estão usando os dígitos extras. Por que isso é um problema? Porque contamos com RANK e COUNTIF para usarem o mesmo número de dígitos. Com uma função de 15 dígitos e a outra de 17 dígitos, você tem um problema.
Por enquanto, a solução parece ser converter todas as suas respostas usando =ROUND(A4,15).
Toda sexta-feira, examino um bug ou outro comportamento suspeito no Excel. Esse erro de cálculo é difícil de detectar e é considerado um peixe grande.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Cada vez que você mescla células, você mata um gatinho"
Szilvia Juhasz
