Обща формула
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Обобщение
За да изчислите средното заплащане на седмица, с изключение на седмиците, в които не са регистрирани часове, и без вече изчислено общо заплащане на седмица, можете да използвате формула, базирана на функциите SUMPRODUCT и COUNTIF. В показания пример формулата в J5 е:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
което връща средното заплащане на седмица, с изключение на седмици, в които не са регистрирани часове. Това е формула на масив, но не е необходимо да въвеждате с control + shift + enter, тъй като функцията SUMPRODUCT може да обработва повечето операции с масиви.
Обяснение
Може би първо мислите, че този проблем може да бъде разрешен с функцията AVERAGEIF или AVERAGEIFS. Тъй като обаче общото заплащане на седмица не е част от работния лист, не можем да използваме тези функции, тъй като те изискват обхват.
Работейки отвътре навън, първо изчисляваме общото заплащане за всички седмици:
D5:I5*D6:I6 // total pay for all weeks
Това е операция с масив, която умножава часовете по ставки за изчисляване на седмичните суми на заплащане. Резултатът е масив като този:
(87,63,48,0,12,0) // weekly pay amounts
Тъй като в работния лист има 6 седмици, масивът съдържа 6 стойности. Този масив се връща директно към функцията SUMPRODUCT:
SUMPRODUCT((348,252,192,0,48,0))
След това функцията SUMPRODUCT връща сумата от елементи в масива, 840. В този момент имаме:
=840/COUNTIF(D5:I5,">0")
След това функцията COUNTIF връща брой стойности, по-големи от нула в диапазона D5: I5. Тъй като 2 от 6 стойности са празни и Excel оценява празните клетки като нула, COUNTIF връща 4.
=840/4 =210
Крайният резултат е 840, разделен на 4, което е равно на 210
