CODEWITHSUNDEEP
excelexcel-formula
Reece
Reece

Reputation: 59

Formula for weighted averages

This is a bit of a hybrid between a mathematical and an Excel issue. I currently have an Excel sheet with a list of yearly observations. To simplify, lets say that for five years I'm looking at:

2015=5
2014=3
2013=4
2012=1
2011=6

What I would like to do is write a formula that counts the number of values in question (5 in this case), divides 100% of the weight and and makes each preceding value be worth 10% less than the last.

So in this case

2015 would be worth (roughly rounded) 24%
2014=22%
2013=20%
2012=18%
2011=16%

if you add the weight for each they add up to 100%.

As an example the numbers to be presented for weighting are:

1.225 for 2015 (5*.24)
.6615 for 2014 (3*.22)
.7938 for 2013 (4*.20)
.1786 for 2012 (1*.18)
.9645 for 2011 (6*.16)

I have calculated all of these numbers manually but would need a formula that can adapt to the number of periods being used as I will be adding more over time.

Upvotes: 0

Views: 98

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

Sum of n terms (Sn) of an geometric series starting with a and having ratio r is:

Sn = a(1 − r^n)
    ___________
       1 − r

All you need to do is rearrange and solve for a given Sn=100, r=0.9 and n=number of terms....

a(1 − r^n) = Sn(1-r)

a = Sn(1-r)
    _______
    (1-r^n)

For 5 terms:

a = 100 * (1 - 0.9) / (1 - 0.9^5) = 24.419

for 10 terms:

a = 100 * (1 - 0.9) / (1 - 0.9^10) = 15.353

Upvotes: 0

Related Questions