Fórmula genérica
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Resumo
Para calcular o pagamento médio por semana, excluindo semanas em que nenhuma hora foi registrada e sem o pagamento total por semana já calculado, você pode usar uma fórmula baseada nas funções SUMPRODUCT e COUNTIF. No exemplo mostrado, a fórmula em J5 é:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
que retorna o pagamento médio por semana, excluindo semanas em que nenhuma hora foi registrada. Esta é uma fórmula de matriz, mas não é necessário entrar com control + shift + enter porque a função SUMPRODUCT pode manipular nativamente a maioria das operações de matriz.
Explicação
Você pode primeiro pensar que este problema pode ser resolvido com a função AVERAGEIF ou AVERAGEIFS. No entanto, como o pagamento total por semana não faz parte da planilha, não podemos usar essas funções porque exigem um intervalo.
Trabalhando de dentro para fora, primeiro calculamos o pagamento total para todas as semanas:
D5:I5*D6:I6 // total pay for all weeks
Esta é uma operação de matriz que multiplica horas por taxas para calcular os valores de pagamento semanais. O resultado é uma matriz como esta:
(87,63,48,0,12,0) // weekly pay amounts
Como há 6 semanas na planilha, a matriz contém 6 valores. Esta matriz é retornada diretamente para a função SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
A função SUMPRODUCT retorna a soma dos itens na matriz, 840. Neste ponto, temos:
=840/COUNTIF(D5:I5,">0")
Em seguida, a função CONT.SE retorna uma contagem de valores maior que zero no intervalo D5: I5. Como 2 dos 6 valores estão em branco e o Excel avalia as células em branco como zero, CONT.SE retorna 4.
=840/4 =210
O resultado final é 840 dividido por 4, o que equivale a 210
