Generisk formel
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Sammanfattning
För att beräkna genomsnittlig lön per vecka, exklusive veckor där inga timmar loggades, och utan den totala lönen per vecka som redan beräknats kan du använda en formel baserad på funktionerna SUMPRODUCT och COUNTIF. I exemplet som visas är formeln i J5:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
som returnerar den genomsnittliga lönen per vecka, exklusive veckor där inga timmar loggades. Detta är en matrisformel, men det är inte nödvändigt att ange med kontroll + shift + enter eftersom SUMPRODUCT-funktionen kan hantera de flesta array-operationer.
Förklaring
Du kanske först tror att detta problem kan lösas med funktionen AVERAGEIF eller AVERAGEIFS. Men eftersom total lön per vecka inte ingår i kalkylbladet kan vi inte använda dessa funktioner eftersom de kräver ett intervall.
När vi arbetar inifrån och ut beräknar vi först den totala lönen för alla veckor:
D5:I5*D6:I6 // total pay for all weeks
Detta är array-operation som multiplicerar timmar med räntor för att beräkna veckolön. Resultatet är en array som denna:
(87,63,48,0,12,0) // weekly pay amounts
Eftersom det finns sex veckor i kalkylbladet innehåller matrisen sex värden. Denna matris returneras direkt till SUMPRODUCT-funktionen:
SUMPRODUCT((348,252,192,0,48,0))
Funktionen SUMPRODUCT returnerar sedan summan av objekt i matrisen, 840. Vid denna punkt har vi:
=840/COUNTIF(D5:I5,">0")
Därefter returnerar COUNTIF-funktionen ett antal värden större än noll i området D5: I5. Eftersom 2 av de 6 värdena är tomma och Excel utvärderar tomma celler som noll, returnerar COUNTIF 4.
=840/4 =210
Det slutliga resultatet är 840 dividerat med 4, vilket motsvarar 210
