EXCEL: Pago promedio por semana

Para calcular el pago promedio por semana, excluyendo las semanas donde no se registraron horas, y sin el pago total por semana ya calculado, puede usar una fórmula basada en las funciones SUMAPRODUCTO y CONTAR.SI. En el ejemplo que se muestra, la fórmula en J5 es:

=SUMAPRODUCTO(D5:I5*D6:I6)/CONTAR.SI(D5:I5,">0")

que devuelve el pago promedio por semana, excluyendo las semanas donde no se registraron horas. Esta es una fórmula de matriz, pero no es necesario ingresar con control + shift + enter porque la función SUMAPRODUCTO puede manejar de forma nativa la mayoría de las operaciones de matriz.

Cómo funciona esta fórmula

Primero puede pensar que este problema se puede resolver con la función PROMEDIO.SI o PROMEDIO.SIS. Sin embargo, debido a que el pago total por semana no es parte de la hoja de trabajo, no podemos usar estas funciones porque requieren un rango.

Trabajando de adentro hacia afuera, primero calculamos el pago total de todas las semanas:

D5:I5*D6:I6 // total pay for all weeks

Esta es una operación de matriz que multiplica las horas por las tasas para calcular los montos de pago semanales. El resultado es una matriz como esta:

{87,63,48,0,12,0} // weekly pay amounts

Como hay 6 semanas en la hoja de trabajo, la matriz contiene 6 valores. Esta matriz se devuelve directamente a la función SUMAPRODUCTO:

SUMAPRODUCTO({348,252,192,0,48,0})

La función SUMAPRODUCTO devuelve la suma de elementos en la matriz, 840. En este punto, tenemos:

=840/CONTAR.SI(D5:I5,">0")

A continuación, la función CONTAR.SI devuelve un recuento de valores mayores que cero en el rango D5: I5. Dado que 2 de los 6 valores están en blanco, y Excel evalúa las celdas en blanco como cero, CONTAR.SI devuelve 4.

=840/4
=210

El resultado final es 840 dividido por 4, lo que equivale a 210