Formula generică
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
rezumat
Pentru a calcula salariul mediu pe săptămână, cu excepția săptămânilor în care nu s-au înregistrat ore și fără plata totală pe săptămână deja calculată, puteți utiliza o formulă bazată pe funcțiile SUMPRODUCT și COUNTIF. În exemplul prezentat, formula din J5 este:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
care returnează salariul mediu pe săptămână, cu excepția săptămânilor în care nu au fost înregistrate ore. Aceasta este o formulă de matrice, dar nu este necesar să introduceți cu control + shift + enter deoarece funcția SUMPRODUCT poate gestiona în mod nativ majoritatea operațiunilor de matrice.
Explicaţie
S-ar putea să vă gândiți mai întâi că această problemă poate fi rezolvată cu funcția AVERAGEIF sau AVERAGEIFS. Cu toate acestea, deoarece plata totală pe săptămână nu face parte din foaia de lucru, nu putem folosi aceste funcții, deoarece necesită un interval.
Lucrând din interior spre exterior, calculăm mai întâi salariul total pentru toate săptămânile:
D5:I5*D6:I6 // total pay for all weeks
Aceasta este operațiunea matrice care înmulțește orele cu tarifele pentru a calcula sumele plătite săptămânal. Rezultatul este o matrice ca aceasta:
(87,63,48,0,12,0) // weekly pay amounts
Deoarece există 6 săptămâni în foaia de lucru, matricea conține 6 valori. Această matrice este returnată direct la funcția SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
Funcția SUMPRODUCT returnează apoi suma articolelor din matrice, 840. În acest moment, avem:
=840/COUNTIF(D5:I5,">0")
Apoi, funcția COUNTIF returnează un număr de valori mai mare decât zero în intervalul D5: I5. Deoarece 2 din cele 6 valori sunt goale, iar Excel evaluează celulele goale ca zero, COUNTIF returnează 4.
=840/4 =210
Rezultatul final este 840 împărțit la 4, ceea ce este egal cu 210
