Üldine valem
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Kokkuvõte
Keskmise nädalapalga arvutamiseks, välja arvatud nädalad, kus ühtegi tundi ei registreeritud, ja ilma juba arvutatud nädala töötasuta, võite kasutada funktsioone SUMPRODUCT ja COUNTIF põhinevat valemit. Näidatud näites on valem J5-s:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
mis tagastab keskmise palga nädalas, välja arvatud nädalad, kus tunde ei registreeritud. See on massiivivalem, kuid seda ei ole vaja sisestada juhtklahviga + shift + enter, kuna funktsioon SUMPRODUCT saab enamiku massiivioperatsioonidega algselt hakkama.
Selgitus
Kõigepealt võite arvata, et selle probleemi saab lahendada funktsiooniga AVERAGEIF või AVERAGEIFS. Kuna kogu nädala töötasu ei kuulu töölehe hulka, ei saa me neid funktsioone kasutada, kuna need nõuavad vahemikku.
Seestpoolt välja töötades arvutame kõigepealt kõigi nädalate kogupalga:
D5:I5*D6:I6 // total pay for all weeks
See on massiivioperatsioon, mis korrutab tunde määradega, et arvutada iganädalane palgasumma. Tulemuseks on selline massiiv:
(87,63,48,0,12,0) // weekly pay amounts
Kuna töölehel on 6 nädalat, sisaldab massiiv 6 väärtust. See massiiv tagastatakse otse funktsiooni SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Funktsioon SUMPRODUCT tagastab massiivi üksuste summa 840. Sel hetkel on meil:
=840/COUNTIF(D5:I5,">0")
Järgmisena tagastab funktsioon COUNTIF vahemikus D5: I5 nullist suuremate väärtuste arvu. Kuna 2 kuuest väärtusest on tühjad ja Excel hindab tühjad lahtrid nulliks, tagastab COUNTIF 4.
=840/4 =210
Lõpptulemus on 840 jagatud 4-ga, mis võrdub 210-ga
