Generisk formel
=SUMPRODUCT(weights,values)/SUM(weights)
Sammanfattning
För att beräkna ett viktat medelvärde kan du använda SUMPRODUCT-funktionen tillsammans med SUM-funktionen. I exemplet som visas är formeln i G5, kopierad ner,:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
där vikter är det namngivna intervallet I5: K5.
Förklaring
Ett viktat genomsnitt, även kallat ett viktat medelvärde, är ett genomsnitt där vissa värden räknas mer än andra. Med andra ord har vissa värden mer "vikt". Vi kan beräkna ett viktat medelvärde genom att multiplicera värdena till medelvärdet med motsvarande vikter och sedan dela summan av resultaten med summan av vikterna. I Excel kan detta representeras med den generiska formeln nedan, där vikter och värden är cellintervall:
=SUMPRODUCT(weights,values)/SUM(weights)
I arbetsarket visas, noter för 3 tester visas i kolumnerna C till E, och vikterna är i de nämnda avståndsvikter (I5: K5). Formeln i cell G5 är:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Arbetar inifrån och ut, använder vi först SUMPRODUCT-funktionen för att multiplicera vikter med motsvarande poäng och summera resultatet:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT multiplicerar först motsvarande element i de två matriserna tillsammans och returnerar sedan summan av produkten:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Resultatet divideras sedan med summan av vikterna:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Som formeln kopieras ned kolumn G, de namngivna intervall vikterna I5: K5 ändras inte, eftersom den uppför sig som en absolut referens. Poängen i C5: E5, som anges som en relativ referens, uppdateras dock i varje ny rad. Resultatet är ett viktat genomsnitt för varje namn i listan som visas. Medelvärdet i kolumn F beräknas endast som referens med AVERAGE-funktionen:
=AVERAGE(C5:E5)
Vikt som inte uppgår till 1
I det här exemplet är vikterna konfigurerade för att lägga till upp till 1, så delaren är alltid 1, och resultatet är det värde som returneras av SUMPRODUCT. En trevlig funktion i formeln är dock att vikterna inte behöver lägga upp till 1.
Vi kan till exempel använda vikten 1 för de två första testerna och vikten 2 för finalen (eftersom finalen är dubbelt så viktig) och det vägda genomsnittet kommer att vara detsamma:
I cell G5 löses formeln så här:
=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
Obs! Värdena i lockiga hakparenteser () ovan är områden uttryckta som matriser.
Transportera vikter
SUMPRODUCT-funktionen kräver att arraydimensioner är kompatibla. Om dimensioner inte är kompatibla kommer SUMPRODUCT att returnera ett #VALUE-fel. I exemplet nedan är vikterna desamma som originalexemplet, men de visas i ett vertikalt intervall:
För att beräkna ett viktat genomsnitt med samma formel måste vi "vända" vikterna till en horisontell matris med TRANSPOSE-funktionen så här:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Efter att TRANSPOSE har körts, den vertikala matrisen:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
blir:
=(0.25,0.25,0.5) // horizontal array
Och från denna punkt beter sig formeln som tidigare.
Läs mer: vertikala och horisontella matriser.
