Generička formula
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Sažetak
Da biste izračunali prosječnu plaću tjedno, isključujući tjedne u kojima nije zabilježeno nijedno radno vrijeme, a bez već izračunate ukupne plaće tjedno, možete upotrijebiti formulu koja se temelji na funkcijama SUMPRODUCT i COUNTIF. U prikazanom primjeru formula u J5 je:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
koja vraća prosječnu tjednu plaću, isključujući tjedne u kojima nije zabilježeno radno vrijeme. Ovo je formula niza, ali nije potrebno unijeti s control + shift + enter jer funkcija SUMPRODUCT može izvorno obraditi većinu operacija polja.
Obrazloženje
Prvo biste mogli pomisliti da se ovaj problem može riješiti pomoću funkcije AVERAGEIF ili AVERAGEIFS. Međutim, budući da ukupna plaća tjedno nije dio radnog lista, ne možemo koristiti ove funkcije jer zahtijevaju raspon.
Radeći iznutra prema van, prvo izračunavamo ukupnu plaću za sve tjedne:
D5:I5*D6:I6 // total pay for all weeks
Ovo je operacija niza koja množi sate sa stopama za izračunavanje iznosa tjedne plaće. Rezultat je niz poput ovog:
(87,63,48,0,12,0) // weekly pay amounts
Budući da na radnom listu ima 6 tjedana, niz sadrži 6 vrijednosti. Ovaj se niz vraća izravno u funkciju SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Zatim funkcija SUMPRODUCT vraća zbroj stavki u polju, 840. U ovom trenutku imamo:
=840/COUNTIF(D5:I5,">0")
Dalje, funkcija COUNTIF vraća broj vrijednosti veće od nule u rasponu D5: I5. Budući da su 2 od 6 vrijednosti prazne, a Excel prazne stanice procjenjuje kao nulu, COUNTIF vraća 4.
=840/4 =210
Konačni rezultat je 840 podijeljen s 4, što je jednako 210
