Generieke formule
=SUMPRODUCT(weights,values)/SUM(weights)
Samenvatting
Om een gewogen gemiddelde te berekenen, kunt u de SOMPRODUCT-functie samen met de SOM-functie gebruiken. In het getoonde voorbeeld is de formule in G5, naar beneden gekopieerd,:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
waarbij gewichten het benoemde bereik I5: K5 is.
Uitleg
Een gewogen gemiddelde, ook wel gewogen gemiddelde genoemd, is een gemiddelde waarbij sommige waarden meer tellen dan andere. Met andere woorden, sommige waarden hebben meer "gewicht". We kunnen een gewogen gemiddelde berekenen door de waarden te vermenigvuldigen met het gemiddelde met de overeenkomstige gewichten, en vervolgens de som van de resultaten te delen door de som van de gewichten. In Excel kan dit worden weergegeven met de onderstaande generieke formule, waarbij gewichten en waarden celbereiken zijn:
=SUMPRODUCT(weights,values)/SUM(weights)
In het weergegeven werkblad worden scores voor 3 tests weergegeven in de kolommen C tot en met E, en de gewichten in het genoemde bereikgewichten (I5: K5). De formule in cel G5 is:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
We werken van binnen naar buiten en gebruiken eerst de SUMPRODUCT-functie om gewichten te vermenigvuldigen met overeenkomstige scores en het resultaat op te tellen:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SOMPRODUCT vermenigvuldigt eerst de corresponderende elementen van de twee arrays met elkaar en retourneert vervolgens de som van het product:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Het resultaat wordt vervolgens gedeeld door de som van de gewichten:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Omdat de formule naar beneden in kolom G wordt gekopieerd, verandert de genoemde bereikgewichten I5: K5 niet, aangezien het zich gedraagt als een absolute referentie. De scores in C5: E5, ingevoerd als relatieve referentie, worden echter in elke nieuwe rij bijgewerkt. Het resultaat is een gewogen gemiddelde voor elke naam in de lijst, zoals weergegeven. Het gemiddelde in kolom F wordt alleen ter referentie berekend met de functie GEMIDDELDE:
=AVERAGE(C5:E5)
Gewichten die niet samen 1 zijn
In dit voorbeeld zijn de gewichten geconfigureerd om op te tellen tot 1, dus de deler is altijd 1, en het resultaat is de waarde die wordt geretourneerd door SOMPRODUCT. Een leuke eigenschap van de formule is echter dat de gewichten niet op 1 hoeven te tellen.
We zouden bijvoorbeeld een gewicht van 1 kunnen gebruiken voor de eerste twee tests en een gewicht van 2 voor de finale (aangezien de finale twee keer zo belangrijk is) en het gewogen gemiddelde zal hetzelfde zijn:
In cel G5 wordt de formule als volgt opgelost:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Opmerking: de waarden tussen accolades () hierboven zijn bereiken die worden uitgedrukt als arrays.
Gewichten verplaatsen
De SUMPRODUCT-functie vereist dat matrixdimensies compatibel zijn. Als afmetingen niet compatibel zijn, retourneert SOMPRODUCT de fout #VALUE. In het onderstaande voorbeeld zijn de gewichten hetzelfde als in het oorspronkelijke voorbeeld, maar worden ze weergegeven in een verticaal bereik:
Om een gewogen gemiddelde te berekenen met dezelfde formule, moeten we de gewichten "omdraaien" in een horizontale matrix met de TRANSPONEREN-functie als volgt:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Nadat TRANSPOSE is uitgevoerd, zal de verticale array:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
wordt:
=(0.25,0.25,0.5) // horizontal array
En vanaf dit punt gedraagt de formule zich zoals voorheen.
Lees meer: verticale en horizontale arrays.
