Generisk formel
=SUMPRODUCT(weights,values)/SUM(weights)
Sammendrag
For å beregne et vektet gjennomsnitt kan du bruke SUMPRODUCT-funksjonen sammen med SUM-funksjonen. I eksemplet vist er formelen i G5, kopiert ned,:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
hvor vekter er det navngitte området I5: K5.
Forklaring
Et veid gjennomsnitt, også kalt et vektet gjennomsnitt, er et gjennomsnitt der noen verdier teller mer enn andre. Med andre ord, noen verdier har mer "vekt". Vi kan beregne et veid gjennomsnitt ved å multiplisere verdiene til gjennomsnittet med tilsvarende vekter, og deretter dele summen av resultatene med vekten. I Excel kan dette være representert med den generiske formelen nedenfor, der vekter og verdier er celleområder:
=SUMPRODUCT(weights,values)/SUM(weights)
I regnearket som vises, vises poeng for 3 tester i kolonnene C til E, og vektene er i de nevnte områdevektene (I5: K5). Formelen i celle G5 er:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Når vi arbeider fra innsiden og ut, bruker vi først SUMPRODUCT-funksjonen til å multiplisere vekter med tilsvarende poengsummer og summere resultatet:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT multipliserer først de tilsvarende elementene i de to matriser sammen, og returnerer deretter summen av produktet:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Resultatet blir deretter delt med summen av vektene:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Når formelen er kopiert ned kolonne G, det navngitte området vektene I5: K5 endres ikke, ettersom den oppfører seg som en absolutt referanse. Poengene i C5: E5, som er angitt som en relativ referanse, oppdateres imidlertid i hver nye rad. Resultatet er et vektet gjennomsnitt for hvert navn i listen som vist. Gjennomsnittet i kolonne F beregnes kun for referanse med AVERAGE-funksjonen:
=AVERAGE(C5:E5)
Vekter som ikke tilsvarer 1
I dette eksemplet er vektene konfigurert til å legge opp til 1, slik at divisoren alltid er 1, og resultatet er verdien som returneres av SUMPRODUCT. En fin funksjon av formelen er imidlertid at vektene ikke trenger å legge opp til 1.
For eksempel kan vi bruke en vekt på 1 for de to første testene og en vekt på 2 for finalen (siden finalen er dobbelt så viktig), og det vektede gjennomsnittet vil være det samme:
I celle G5 løses formelen slik:
=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
Merk: verdiene i krøllete bukseseler () ovenfor er områder uttrykt som matriser.
Transportere vekter
SUMPRODUCT-funksjonen krever at arraydimensjoner er kompatible. Hvis dimensjoner ikke er kompatible, vil SUMPRODUCT returnere en #VALUE-feil. I eksemplet nedenfor er vektene de samme som det opprinnelige eksemplet, men de er oppført i et vertikalt område:
For å beregne et vektet gjennomsnitt med samme formel, må vi "vende" vektene til en horisontal matrise med TRANSPOSE-funksjonen slik:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Etter at TRANSPOSE har kjørt, vil den vertikale matrisen:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
blir:
=(0.25,0.25,0.5) // horizontal array
Og fra dette punktet oppfører formelen seg som før.
Les mer: vertikale og horisontale matriser.
