Calculando médias por critérios com AVERAGEIF

Você já totalizou com SUMIF e contou com COUNTIF. O terceiro membro da família é AVERAGEIF, que calcula a média apenas dos valores que atendem a uma condição.

Em vez de calcular a média de todas as vendas, você pode calcular apenas a média das vendas da região Leste ou apenas das pontuações acima de 50. Isso combina um filtro e uma média em uma única etapa simples.

AVERAGEIF usa o mesmo formato de três partes que SUMIF:

• intervalo as células a testar
• critério a condição
• intervalo_da_média as células cuja média será calculada

Ele percorre o intervalo, encontra as correspondências e calcula a média das células correspondentes em intervalo_da_média.

Com regiões em A2:A10 e valores em B2:B10, a média de vendas da região Leste testa a coluna A quanto a Leste e calcula a média dos valores correspondentes na coluna B.

Nos bastidores, isso equivale a dividir um SUMIF por um COUNTIF, mas AVERAGEIF reúne tudo em uma única fórmula fácil de ler.

Se a coluna que você testa também for a coluna cuja média deseja calcular, poderá omitir o terceiro argumento. Para calcular a média apenas das pontuações em B2:B10 que sejam maiores que 70:

Aqui, a coluna B é usada tanto para o teste quanto para o cálculo da média. Quando você omite intervalo_da_média, a planilha usa intervalo para ambas as finalidades.

Assim como nas outras funções IF, armazene a condição em uma célula para obter flexibilidade. Se D1 contiver o nome de uma região, aponte o critério para D1.

Agora uma única célula controla a média. Alterar D1 de Leste para Oeste recalcula o resultado imediatamente, o que é ideal para uma tabela de resumo ou um seletor de painel.

AVERAGEIF aceita operadores entre aspas, assim como suas funções irmãs. Para calcular a média apenas das vendas de pelo menos 100:

• ">=100" 100 ou mais
• "<50" abaixo de 50
• "<>0" excluindo zeros

Essa última opção é muito útil porque calcula a média ignorando entradas zero que, de outra forma, reduziriam o valor médio.

Para usar um limite vindo de uma célula, una o operador a ela com um e comercial. Se D1 contiver um limite, calcule a média de tudo que estiver acima dele.

O & cria o texto da condição a partir de ">" e do valor em D1. Este é o mesmo truque de união usado com SUMIF e COUNTIF.

AVERAGEIF tem uma armadilha que as outras funções não têm. Se nenhuma linha corresponder, não haverá nada para dividir, e você obterá um erro #DIV/0!.

Por exemplo, calcular a média de uma região que não existe nos dados retorna esse erro. A planilha está informando que o filtro não encontrou nenhuma célula, e não que sua fórmula está incorreta.

Envolva a fórmula em IFERROR para mostrar uma mensagem amigável em vez de #DIV/0! quando nada corresponder.

Agora, uma região vazia exibirá o texto Sem dados, em vez de um erro alarmante. Isso mantém os relatórios com uma aparência organizada mesmo quando algumas categorias estão ausentes.

AVERAGEIF calcula a média apenas das células que contêm números entre as correspondências. Células realmente vazias no intervalo_da_média são ignoradas, portanto não são contadas como zero.

Isso é importante: um valor ausente não puxará a média em direção a zero como faria um 0 literal. Se também quiser excluir zeros, adicione o critério "<>0" à coluna de valores.

Crie um resumo organizado: liste cada categoria nas células D2, D3 e D4; depois, escreva um AVERAGEIF referenciando a célula do rótulo e preencha para baixo.

Agora cada linha mostra o valor médio de sua própria categoria, lado a lado com o total do SUMIF e a contagem do COUNTIF das lições anteriores. Juntos, eles formam um relatório compacto, totalmente controlado por fórmulas.

Agora você pode calcular médias condicionalmente com AVERAGEIF. Pontos principais:

• A ordem é intervalo, critério, intervalo_da_média; omita o último argumento para calcular a média do próprio intervalo testado.
• A função aceita texto, números e operadores como ">=100".
• Nenhuma correspondência produz um erro #DIV/0!; portanto, proteja a fórmula com IFERROR.
• Células vazias são ignoradas, não tratadas como zero.

A seguir, você fará correspondências de texto parcial usando caracteres curinga.