Splošna formula
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Povzetek
Za izračun povprečnega plačila na teden, razen tednov, v katerih ni bila zabeležena nobena ura, in brez že izračunanega skupnega plačila na teden lahko uporabite formulo, ki temelji na funkcijah SUMPRODUCT in COUNTIF. V prikazanem primeru je formula v J5:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
ki vrne povprečno plačilo na teden, razen tednov, v katerih ni bilo zabeleženih ur. To je formula matrike, vendar je ni treba vnašati s control + shift + enter, ker lahko funkcija SUMPRODUCT prvotno obdela večino operacij matrike.
Pojasnilo
Morda najprej pomislite, da je to težavo mogoče rešiti s funkcijo AVERAGEIF ali AVERAGEIFS. Ker pa skupno plačilo na teden ni del delovnega lista, teh funkcij ne moremo uporabljati, ker zahtevajo obseg.
Če delamo od znotraj navzven, najprej izračunamo skupno plačo za vse tedne:
D5:I5*D6:I6 // total pay for all weeks
To je niz, ki množi ure s stopnjami za izračun tedenskih zneskov plač. Rezultat je matrika, kot je ta:
(87,63,48,0,12,0) // weekly pay amounts
Ker je na delovnem listu 6 tednov, matrika vsebuje 6 vrednosti. Ta matrika se vrne neposredno v funkcijo SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Nato funkcija SUMPRODUCT vrne vsoto elementov v matriki, 840. Na tej točki imamo:
=840/COUNTIF(D5:I5,">0")
Nato funkcija COUNTIF vrne število vrednosti, večjih od nič v območju D5: I5. Ker sta 2 od 6 vrednosti prazni in Excel prazne celice oceni kot nič, COUNTIF vrne 4.
=840/4 =210
Končni rezultat je 840, deljeno s 4, kar je enako 210
