Excel

Vägt genomsnitt

Weighted Average

Excel -formel: Vägt genomsnittGenerisk formel | _+_ | Sammanfattning

För att beräkna ett vägt genomsnitt kan du använda SUMPRODUKT fungerar tillsammans med SUM -funktion . I exemplet som visas är formeln i G5, kopierad ner:



= SUMPRODUCT (weights,values)/ SUM (weights)

var vikter är namngivet intervall I5: K5.

Förklaring

Ett vägat medelvärde, även kallat ett vägat medelvärde, är ett genomsnitt där vissa värden räknas mer än andra. Med andra ord har vissa värden större vikt. Vi kan beräkna ett vägt medelvärde genom att multiplicera värdena till medelvärdet med motsvarande vikter och sedan dela summan av resultaten med summan av vikter. I Excel kan detta representeras med den generiska formeln nedan, där vikter och värden är cellområden:





mac excel new line in cell
 
= SUMPRODUCT (weights,C5:E5)/ SUM (weights)

I det kalkylblad som visas visas poäng för 3 tester i kolumnerna C till E och vikterna finns i namngivet intervall vikter (I5: K5). Formeln i cell G5 är:

 
= SUMPRODUCT (weights,values)/ SUM (weights)

Genom att arbeta 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)/ SUM (weights)

SUMPRODUCT multiplicerar först motsvarande element i de två matriserna tillsammans och returnerar sedan produktens summa:

 
= SUMPRODUCT (weights,C5:E5) // returns 88.25

Resultatet divideras sedan med summan av vikterna:

 
= SUMPRODUCT ({0.25,0.25,0.5},{90,83,90}) = SUMPRODUCT ({22.5,20.75,45}) =88.25

När formeln kopieras ner i kolumn G, det namngivna intervallet vikter I5: K5 ändras inte, eftersom det beter sig som en absolut referens . Men poängen i C5: E5, angiven som en relativ referens , uppdatering i varje ny rad. Resultatet är ett vägt genomsnitt för varje namn i listan som visas. Medelvärdet i kolumn F beräknas endast som referens med MEDEL -funktion :

 
=88.25/ SUM (weights) =88.25/ SUM ({0.25,0.25,0.5}) =88.25/1 =88.25

Vikter som inte summeras till 1

I det här exemplet är vikterna konfigurerade att lägga till upp till 1, så divisorn är alltid 1, och resultatet är värdet som returneras av SUMPRODUCT. En trevlig egenskap med formeln är dock att vikterna inte behöver lägga till upp till 1.

Till exempel kan vi använda en vikt på 1 för de två första testerna och en vikt för 2 för finalen (eftersom finalen är dubbelt så viktig) och det vägda genomsnittet är detsamma:

Excel -viktat genomsnitt med anpassade vikter

slå in texten i den valda cellen.

I cell G5 löses formeln så här:

 
= AVERAGE (C5:E5)

Obs! Värdena i lockparenteser {} ovan är intervall uttryckta som matriser .

Transponerar vikter

SUMPRODUCT -funktionen kräver att matrisdimensioner är kompatibla. Om måtten inte är kompatibla returnerar SUMPRODUCT ett #VÄRDE -fel. I exemplet nedan är vikterna desamma som originalexemplet, men de är listade i a vertikal räckvidd:

Excel vägde genomsnittet med TRANSPOSE

För att beräkna ett vägt genomsnitt med samma formel måste vi 'vända' vikterna till en horisontell matris med TRANSPOSE -funktion 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

Efter TRANSPOSE körs den vertikala matrisen:

 
= SUMPRODUCT ( TRANSPOSE (weights),C5:E5)/ SUM (weights)

blir:

 
= TRANSPOSE ({0.250.250.5}) // vertical array

Och från denna punkt uppför sig formeln som tidigare.

Läs mer: vertikala och horisontella matriser .

Bilagor Fil exceljet viktat genomsnitt. xlsx Författare Dave Bruns


^