Formula generica
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Sommario
Per calcolare la retribuzione media settimanale, escluse le settimane in cui non sono state registrate ore e senza la retribuzione totale settimanale già calcolata, è possibile utilizzare una formula basata sulle funzioni SUMPRODUCT e COUNTIF. Nell'esempio mostrato, la formula in J5 è:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
che restituisce la retribuzione media settimanale, escluse le settimane in cui non sono state registrate ore. Questa è una formula di matrice, ma non è necessario inserirla con CTRL + MAIUSC + INVIO perché la funzione SUMPRODUCT può gestire in modo nativo la maggior parte delle operazioni di matrice.
Spiegazione
Per prima cosa potresti pensare che questo problema possa essere risolto con la funzione MEDIA.SE o MEDIA.SE. Tuttavia, poiché la retribuzione totale settimanale non fa parte del foglio di lavoro, non possiamo utilizzare queste funzioni perché richiedono un intervallo.
Lavorando dall'interno verso l'esterno, calcoliamo innanzitutto la retribuzione totale per tutte le settimane:
D5:I5*D6:I6 // total pay for all weeks
Questa è un'operazione di matrice che moltiplica le ore per le tariffe per calcolare gli importi della paga settimanale. Il risultato è un array come questo:
(87,63,48,0,12,0) // weekly pay amounts
Poiché ci sono 6 settimane nel foglio di lavoro, l'array contiene 6 valori. Questo array viene restituito direttamente alla funzione SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
La funzione SUMPRODUCT restituisce quindi la somma degli elementi nell'array, 840. A questo punto, abbiamo:
=840/COUNTIF(D5:I5,">0")
Successivamente, la funzione CONTA.SE restituisce un conteggio di valori maggiori di zero nell'intervallo D5: I5. Poiché 2 dei 6 valori sono vuoti e Excel valuta le celle vuote come zero, CONTA.SE restituisce 4.
=840/4 =210
Il risultato finale è 840 diviso 4, che è uguale a 210
