Jak obliczyć średnią ważoną w Excelu

Średnia ważona jest średnią ze zbioru obserwacji, przy czym poszczególnym elementom nadawane są wagi. Średnia arytmetyczna dla porównania nadaje wszystkim obserwacjom tę samą wagę – 1/n, gdzie n to liczba wszystkich obserwacji.  W średniej ważonej te wagi są różne i ostatecznie miara ta jest bardziej użyteczną w obliczaniu chociażby zwrotu z portfela inwestycji albo ostatecznej oceny po serii egzaminów na uczelni.

Wzór na średnią ważoną

Średnia ważona dla zbioru obserwacji x1, x2,…, xn, z odpowiadającymi wagami w1,w2,…,wn jest kalkulowana następującym wzorem:

Średnia zwrotu z portfela

Do prostego zestawienia wykorzystajmy notowania z ostatnich kwartałów z serwisu stockwatch dla spółek KGHM Polska Miedź SA oraz CD Projekt SA.

Załóżmy, że w naszym portfelu posiadamy dwa aktywa – akcje wyżej wymienionych spółek. Przyjmijmy dla prostoty obliczeń portfel o wartości 10 000 PLN. 9000 przeznaczone na CDPR i 1000 na KGHM w drugim kwartale 2021. Licząc procentowy zwrot średnią arytmetyczną będziemy pod kreską, co nijak ma się do rzeczywistej zmiany wartości portfela. Prawidłowa zmiana powinna w 90% uwzględnić wzrost 13,6% i w 10% spadek 19,5%. W przybliżeniu da nam to wzrost o 10,29%

Załóżmy czysto hipotetycznie na kolejne kwartały stałe wagi obu składowych. W ten sposób na cały okres trwania inwestycji stosunek nadal by wynosił 90% i 10%. Niestety, ze względu zmiany notowań udziały w portfelu będą ulegać relatywnym zmianom. Dlatego też jedną z możliwości jest utrzymywanie stałych proporcji poprzez bilansowanie składowymi. Przy takim założeniu zwrot z portfela dla kolejnych kwartałów wyglądałby następująco:

• Zwrot z portfela za Q3 2021 = 90% * -9,77% + 10% * 13,43% =  -7,45%
• Zwrot z portfela za Q4 2021 = 90% * -33,57% + 10% * -29,05% =  -33,12%
• Zwrot z portfela za Q1 2022 = 90% * 27,00% + 10% * -8,13% =  23,49%

W przeciwnym wypadku wagi wyglądałyby zupełnie inaczej i już w Q3 2021 udział KGHM spadłby do 7,30%.

Średnia ważona z ocen

Innym przykładem może być średnia z ocen z danego przedmiotu dla dwóch studentów.

Załóżmy, że w czasie roku występują kolokwia i egzamin. Każde z nich ma przypisaną ocenę ale też wagę cząstkową w postaci punktowej skali, gdzie egzamin ma wartość 100, czyli jest decydującym czynnikiem czy student zaliczy rok. W takiej formie jesteśmy z góry ustalić poszczególne wagi oraz to, że są one takie same dla każdego studenta.

W takim przypadku możemy posłużyć się funkcją suma.iloczynów, ponieważ zarówno wagi jak i oceny są po prostu tablicami i należy zsumować iloczyny poszczególnych elementów tychże tablic.

Średnia ważona jest miarą szeroko wykorzystywaną w wielu dziedzinach nauki zarówno technicznych, finansowych jak i społecznych. Obok średniej arytmetycznej, geometrycznej, mediany oraz kwartyli jest jedną z najbardziej znanych miar tendencji centralnej