Generieke formule
=SUMPRODUCT(hours*rate)/COUNTIF(hours,">0")
Samenvatting
Om het gemiddelde loon per week te berekenen, exclusief weken waarin geen uren zijn geregistreerd en zonder dat het totale loon per week al is berekend, kunt u een formule gebruiken die is gebaseerd op de functies SOMPRODUCT en AANTAL.ALS. In het getoonde voorbeeld is de formule in J5:
=SUMPRODUCT(D5:I5*D6:I6)/COUNTIF(D5:I5,">0")
die het gemiddelde loon per week retourneert, exclusief weken waarin geen uren zijn geregistreerd. Dit is een matrixformule, maar het is niet nodig om in te voeren met control + shift + enter omdat de SOMPRODUCT-functie de meeste matrixbewerkingen kan verwerken.
Uitleg
U denkt misschien eerst dat dit probleem kan worden opgelost met de functie AVERAGEIF of AVERAGEIFS. Omdat het totale loon per week echter geen deel uitmaakt van het werkblad, kunnen we deze functies niet gebruiken omdat ze een bereik vereisen.
We werken van binnen naar buiten en berekenen eerst het totale loon voor alle weken:
D5:I5*D6:I6 // total pay for all weeks
Dit is een matrixbewerking die uren vermenigvuldigt met tarieven om wekelijkse loonbedragen te berekenen. Het resultaat is een array als deze:
(87,63,48,0,12,0) // weekly pay amounts
Aangezien het werkblad 6 weken bevat, bevat de matrix 6 waarden. Deze array wordt direct geretourneerd naar de SOMPRODUCT-functie:
SUMPRODUCT((348,252,192,0,48,0))
De functie SOMPRODUCT retourneert vervolgens de som van de items in de array, 840. Op dit punt hebben we:
=840/COUNTIF(D5:I5,">0")
Vervolgens retourneert de functie AANTAL.ALS een aantal waarden groter dan nul in het bereik D5: I5. Aangezien 2 van de 6 waarden leeg zijn en Excel lege cellen evalueert als nul, geeft AANTAL.ALS als resultaat 4.
=840/4 =210
Het uiteindelijke resultaat is 840 gedeeld door 4, wat gelijk is aan 210
