Gewogen gemiddelde berekenen in Excel met SOMPRODUCT

Bij een gewoon gemiddelde telt elk getal even zwaar. Bij een gewogen gemiddelde krijgt elk getal een gewicht. Een proefwerk met weging 3 telt drie keer zo zwaar als een overhoring met weging 1.

Zo reken je het uit de hand:

• vermenigvuldig elk cijfer met zijn weging
• tel die uitkomsten op
• deel door de som van de wegingen

Stel: een 7 met weging 1, een 5,5 met weging 2 en een 8 met weging 3. Dat is 7 + 11 + 24 = 42, gedeeld door 1 + 2 + 3 = 6. Het gewogen gemiddelde is 7.

Het gewone gemiddelde van deze drie cijfers is maar 6,83. Daar telt de 8 net zo licht als de rest, terwijl die juist het zwaarst hoort te wegen.

In Excel doe je die drie stappen in één formule:

=SOMPRODUCT(B2:B6;C2:C6)/SOM(C2:C6)

SOMPRODUCT vermenigvuldigt de twee bereiken rij voor rij en telt de uitkomsten op. Het rekent dus B2 x C2 + B3 x C3 en zo verder. Daarna deel je door SOM(C2:C6), de som van alle wegingen.

Let op een paar dingen:

• beide bereiken zijn even groot en beginnen in dezelfde rij, anders krijg je #WAARDE!
• de volgorde van de twee bereiken in SOMPRODUCT maakt niet uit
• deel door de som van de wegingen, niet door het aantal cijfers

Wil je het eindcijfer op één decimaal, zet er dan AFRONDEN omheen: =AFRONDEN(SOMPRODUCT(B2:B6;C2:C6)/SOM(C2:C6);1).

Wegingen kun je als gewone getallen schrijven, zoals 1, 2 en 3, of als percentages, zoals 20%, 30% en 50%. De formule werkt voor allebei.

Tellen de percentages precies op tot 100%, dan is SOM(C2:C6) gelijk aan 1. Dan zou SOMPRODUCT alleen ook genoeg zijn. Laat de deling toch staan. Tellen de wegingen per ongeluk op tot 90% of 110%, dan klopt je uitkomst nog steeds.

Het maakt ook niet uit of je 1, 2 en 3 gebruikt of 10, 20 en 30. Alleen de verhouding tussen de wegingen telt, want je deelt altijd door hun som. Gebruik dus gewoon de wegingen zoals ze in je studiewijzer of PTA staan.

Halverwege het jaar zijn nog niet alle cijfers bekend. Een lege cel telt in SOMPRODUCT als 0, maar de weging telt in SOM wel mee. Je gemiddelde lijkt dan veel te laag. Deel daarom alleen door de wegingen van cijfers die er al zijn:

=SOMPRODUCT(B2:B5;C2:C5)/SOM.ALS(B2:B5;"<>";C2:C5)

SOM.ALS telt hier alleen de wegingen op van rijen waarin kolom B niet leeg is.

Wil je weten welk cijfer je nog moet halen voor een 5,5 gemiddeld, met het laatste cijfer in rij 5? Dan reken je terug:

=(5,5*SOM(C2:C5)-SOMPRODUCT(B2:B4;C2:C4))/C5

Komt daar meer dan 10 uit, dan is een voldoende niet meer haalbaar. Komt er een laag getal uit, dan zit je al bijna goed.

GEMIDDELDE telt alle getallen even zwaar. Dat is prima als elk getal even belangrijk is. Zodra het ene getal meer gewicht heeft dan het andere, geeft GEMIDDELDE een vertekend beeld. Hebben alle wegingen dezelfde waarde, dan geven beide formules hetzelfde.

Een gewogen gemiddelde kom je ook buiten school tegen:

• de gemiddelde inkoopprijs, gewogen naar het aantal stuks per levering
• het gemiddelde cijfer van een opleiding, gewogen naar studiepunten
• de gemiddelde marge, gewogen naar de omzet per product

De formule is steeds dezelfde: =SOMPRODUCT(waarden;gewichten)/SOM(gewichten). Bij de inkoopprijs zijn de prijzen de waarden en de aantallen de gewichten.