Fórmula quebrada na planilha: quanto custa o erro

Fórmula quebrada custa caro porque ela não avisa. Ela devolve um número, o número entra no relatório, o relatório vira decisão. Quando alguém percebe, a decisão já foi tomada e o dinheiro já saiu.

Se você prefere um arquivo desenhado para não quebrar quando alguém arrasta uma linha, a gente faz: planilha sob medida com conferência embutida.

A gente entrou nessa conta porque um gestor estava cobrando a equipe por um atraso que não existia. O painel dele dizia uma coisa, a obra dizia outra, e os dois números saíam do mesmo arquivo. Erro de fórmula não pinta a célula de vermelho nem trava a planilha: ele devolve um número bem formatado, com casa decimal, igual a qualquer outro do relatório.

Numa distribuidora que instala proteção hospitalar, uma soma que parava na primeira linha mostrou 171% de execução numa obra. A obra tinha dois lançamentos de 21 metros, 42 metros no total. A conta pegava só o primeiro.

Percentual de execução acima de 100 parece defeito de tela, mas é o mesmo número que sustenta a medição, a fatura e a liberação de pagamento da obra.

O erro: a soma que para antes do fim

A fórmula quebrada de intervalo tem sempre a mesma origem: a base ganhou linhas novas e o intervalo escrito na fórmula continuou do tamanho antigo.

A obra recebeu duas medições. Cada uma de 21 metros. A soma da execução apontava para um intervalo que cobria a primeira linha e ignorava a segunda. O resultado saiu como número limpo, com casa decimal e tudo, e foi para o painel.

O oposto também acontece. Fórmula puxada até a linha 10.000 numa base com 435 registros carrega 9.565 linhas de fórmula vazia. Essas linhas somam zero, mas contam. Qualquer média, contagem ou percentual que passe por ali sai errado, e sai errado para menos.

Os dois casos têm em comum o fato de a célula não sinalizar nada: não aparece mensagem de erro, o valor sai formatado como qualquer outro e ninguém tem motivo para desconfiar dele. Numa planilha sob medida com fórmula à vista, o total abre até as linhas que o formaram, e o número estranho aparece na hora.

Nessa mesma empresa, a instalação leva 33 dias para mil metros. São 30,3 metros por dia. Uma obra de 42 metros é pouco mais de um dia de equipe. E mesmo assim o painel dizia 171%, número que faz o gestor perguntar por que a equipe está estourando o previsto quando ela não está.

Onde mais a mesma quebra apareceu

Não foi um erro isolado. Foi o mesmo tipo de erro em cinco telas diferentes, todas alimentadas pela mesma lógica de soma.

As vendas reais eram 9, somando R$71.588,48. Isso dá R$7.954,28 por venda. Nenhum desses três números aparecia junto no painel, então ninguém tinha como cruzar.

A conta do prejuízo: R$18.500 por mês

Pegue a linha do ponto de equilíbrio, que é a mais cara da tabela.

A estrutura da empresa era custo fixo de R$100 mil por mês e custo variável de 50% sobre a venda. Com 50% de custo variável, cada real vendido deixa R$0,50 de margem de contribuição.

Ponto de equilíbrio correto:
100.000 ÷ 0,50 = R$200.000 de faturamento por mês.

A planilha mostrava R$163.000.

Agora o custo de acreditar no número errado. Fature os R$163.000 que o painel chamava de equilíbrio:

Margem de contribuição: 163.000 × 0,50 = R$81.500.
Custo fixo: R$100.000.
Resultado do mês: R$81.500 menos R$100.000 = prejuízo de R$18.500.

Doze meses assim dão R$222.000. E esse valor ainda é só a parte contábil, porque em cima dele vêm as decisões: comissão paga sobre meta batida, contratação aprovada, compra de estoque programada, tudo apoiado num mês que fechou no vermelho e foi comemorado.

O DRE contava a mesma história. Ele mostrou R$135 mil de lucro sobre R$264 mil de faturamento. São 51% de margem, percentual que nenhuma distribuidora entrega quando revende produto de terceiro com equipe de instalação própria.

Refaça com a estrutura de custo da própria empresa:
Custo variável: 264.000 × 50% = R$132.000.
Custo fixo: R$100.000.
Custo total: R$232.000.
Resultado: 264.000 menos 232.000 = R$32.000.

O DRE dizia R$135 mil. A conta grosseira dá R$32 mil. São R$103 mil de lucro que existiam só porque os itens sem classificação de fixo ou variável ficavam de fora da soma.

Quando o erro é de método, não de célula

Numa locadora de veículos, o problema tinha outra cara e a mesma raiz. A precificação rodava em duas planilhas Excel ligadas, com macro escrita pelo próprio dono. A taxa interna de retorno saía do Solver, por tentativa e erro.

Tentativa e erro tem um defeito específico: o resultado depende de onde você começou. Duas pessoas rodando a mesma planilha, com chutes iniciais diferentes, chegam a taxas diferentes. E nenhuma das duas fica registrada.

Quanto isso custa. Use um carro de R$100 mil, número redondo para a conta ficar visível, num contrato de 24 meses.

A 1,5% ao mês, o contrato precisa devolver 100.000 × 1,015²⁴ = R$142.950.
A 1,2% ao mês, devolve 100.000 × 1,012²⁴ = R$133.149.

Diferença de R$9.801 por carro, por 0,3 ponto de taxa ao mês. Multiplique pelo tamanho da frota.

Some a isso o que ele disse sobre a operação: “quem tá alimentando ela toda vez sou eu”. Uma pessoa só sabe rodar a planilha. E o medo dele com o vendedor era o de sempre em planilha compartilhada: “digitar sem querer em cima de algum dado”.

Esses dois medos têm conserto dentro da própria planilha: aba de entrada separada da aba de cálculo, células de fórmula protegidas e a taxa saindo de uma função pronta, sem chute inicial. Quem monta a planilha resolve isso uma vez e ninguém mais pensa no assunto.

Como se descobre

Cinco testes que pegam quase toda fórmula quebrada, e que você faz numa tarde.

Teste da última linha. Adicione um registro no fim da base, com valor conhecido e redondo, tipo R$1.000. Se o total não subir exatamente R$1.000, o intervalo da soma está curto.

Teste do total de controle. Some a coluna por fora, com uma soma nova em uma célula solta, e compare com o total do relatório. Bateu, a fórmula pega tudo. Não bateu, você já tem a diferença para caçar.

Teste do percentual impossível. Qualquer percentual de execução acima de 100, qualquer margem acima do que o setor entrega, qualquer ponto de equilíbrio abaixo do custo fixo. Número estranho é fórmula quebrada até prova em contrário.

Teste das duas fontes. Conte os registros de um lado e do outro. Foi assim que apareceu 1.453 contra 4.020.

Teste do registro repetido. Lance duas medições iguais no mesmo pedido, como os dois lançamentos de 21 metros. Se a conta tratar as duas como uma, o erro aparece na hora.

Como não acontecer de novo

Fórmula com intervalo fixo funciona no dia em que foi escrita e passa a devolver número errado no dia em que a base ultrapassa a última linha que ela cobre.

A correção é tirar o intervalo da mão de quem usa. Em planilha isso tem nome: tabela nomeada no lugar de intervalo fixo, e SOMASES em cima dela no lugar de uma SOMA de A2 até A50. A tabela cresce, a fórmula cresce junto, e ninguém precisa lembrar de nada.

O total de uma obra passa a ser a soma de todos os lançamentos daquela obra, sempre. O total de um vendedor é a soma das vendas com o código dele, e uma linha de venda sem vendedor não deveria nem ser aceita.

Três hábitos que evitam a maior parte dos casos:

Nenhum número de decisão sai de célula solta. Ele sai de uma fórmula em cima da tabela inteira, com o filtro à vista.
Todo painel mostra a contagem junto com o valor. Quando aparece “R$106.000 em 9 vendas”, dá para conferir a média e sentir o cheiro.
Todo item precisa de classificação para entrar no relatório. Item sem classificação não some da conta, ele aparece numa lista de pendências.

Perguntas frequentes

Como saber se a fórmula da minha planilha está pegando todas as linhas

Adicione um registro no fim da base com valor redondo, tipo R$1.000, e olhe o total. Se ele não subir exatamente R$1.000, o intervalo da soma está curto. Foi assim que apareceu uma obra com 42 metros em dois lançamentos de 21 metros, onde a conta lia só o primeiro e mostrava 171% de execução.

Quanto custa um erro de fórmula na planilha?

Custa o valor da decisão tomada em cima do número errado. Uma planilha que mostrava ponto de equilíbrio de R$163.000 quando o real era R$200.000 fazia a empresa comemorar um mês com prejuízo de R$18.500. Em doze meses, R$222.000. O custo não é a célula, é a comissão paga e a contratação aprovada.

Por que o percentual de execução da obra passou de 100%?

Porque a soma está lendo parte dos lançamentos. Uma obra com duas medições de 21 metros, 42 no total, apareceu com 171% de execução porque a fórmula cobria a primeira linha e ignorava a segunda. Percentual de execução acima de 100 é fórmula quebrada até prova em contrário, e ele libera medição e pagamento.

Vale a pena refazer a planilha ou é melhor consertar as fórmulas?

Conserto pontual resolve enquanto o erro for um. Nessa distribuidora eram seis telas erradas pela mesma lógica de soma, com R$103 mil de lucro que não existia e 2.567 clientes fora do painel. Quando o mesmo defeito aparece em mais de duas telas, refazer com tabela nomeada sai mais barato que caçar célula todo mês.

Leia também

• Como calcular o ponto de equilíbrio do seu negócio
• Controle de medição de obra e metragem executada
• Precificação de locação de veículos com TIR

Rode os cinco testes na sua planilha essa semana

Comece pelo teste da última linha, que leva dois minutos e pega o erro mais comum. Depois compare o total de um relatório com uma soma feita por fora. Se os dois baterem, sua planilha está inteira hoje. Repita quando a base crescer.

Se der diferença e você não quiser passar o mês caçando célula, a gente refaz a sua planilha sob medida: tabela nomeada, fórmula que cresce com a base, contagem ao lado de todo valor e nenhum intervalo escrito na mão.

Manda uma mensagem contando qual número da sua planilha você já desconfia.

Planilha sob medida com fórmula auditável