Generisk formel
=SUMPRODUCT(weights,values)/SUM(weights)
Resumé
For at beregne et vægtet gennemsnit kan du bruge SUMPRODUCT-funktionen sammen med SUM-funktionen. I det viste eksempel er formlen i G5, kopieret ned,:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
hvor vægte er det navngivne område I5: K5.
Forklaring
Et vægtet gennemsnit, også kaldet et vægtet gennemsnit, er et gennemsnit, hvor nogle værdier tæller mere end andre. Med andre ord har nogle værdier mere "vægt". Vi kan beregne et vægtet gennemsnit ved at gange værdierne til gennemsnittet med tilsvarende vægte og derefter dividere summen af resultaterne med summen af vægte. I Excel kan dette repræsenteres med den generiske formel nedenfor, hvor vægte og værdier er celleområder:
=SUMPRODUCT(weights,values)/SUM(weights)
I regnearket vist, scorer for 3 forsøg fremgår i kolonne C til E, og vægte er i det navngivne område vægte (I5: K5). Formlen i celle G5 er:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Når vi arbejder indefra og ud, bruger vi først SUMPRODUCT-funktionen til at multiplicere vægte med tilsvarende score og summere resultatet:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT multiplicerer først de tilsvarende elementer i de to arrays sammen og returnerer derefter produktets sum:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Resultatet divideres derefter med summen af vægten:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Da formlen kopieres ned i kolonne G, ændres den navngivne rækkevægt I5: K5 ikke, da den opfører sig som en absolut reference. Scorerne i C5: E5, der er angivet som en relativ reference, opdateres imidlertid i hver nye række. Resultatet er et vægtet gennemsnit for hvert navn på listen som vist. Gennemsnittet i kolonne F beregnes kun til reference med funktionen GENNEMSNIT:
=AVERAGE(C5:E5)
Vægte, der ikke svarer til 1
I dette eksempel er vægtene konfigureret til at tilføje op til 1, så deleren altid er 1, og resultatet er den værdi, der returneres af SUMPRODUCT. En god egenskab ved formlen er dog, at vægten ikke behøver at tilføje op til 1.
For eksempel kunne vi bruge en vægt på 1 til de to første tests og en vægt på 2 til finalen (da finalen er dobbelt så vigtig), og det vægtede gennemsnit vil være det samme:
I celle G5 løses formlen således:
=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
Bemærk: værdierne i krøllede seler () ovenfor er områder udtrykt som arrays.
Transportere vægte
SUMPRODUCT-funktionen kræver, at arraydimensioner er kompatible. Hvis dimensioner ikke er kompatible, returnerer SUMPRODUCT en #VALUE-fejl. I nedenstående eksempel er vægtene de samme som det originale eksempel, men de er anført i et lodret område:
For at beregne et vægtet gennemsnit med den samme formel er vi nødt til at "vende" vægtene ind i et vandret array med TRANSPOSE-funktionen som denne:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Efter TRANSPOSE kører, den lodrette matrix:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
bliver til:
=(0.25,0.25,0.5) // horizontal array
Og fra dette punkt opfører formlen sig som før.
Læs mere: lodrette og vandrette arrays.
