How to make Weighted Average

H

H.C. Chen

1. I have a column of values in A:A
2. I am trying to make B:B the Weighted Average of A:A where the Weighted
Average is defined as,
WA5 of B3 = (1*A1+2*A2+3*A3+2*A4+1*A5)/(1+2+3+2+1)
WA5 of B4 = (1*A2+2*A3+3*A4+2*A5+1*A6)/(1+2+3+2+1)
WA5 of B5 = (1*A3+2*A4+3*A5+2*A6+1*A7)/(1+2+3+2+1)
You can see B:B will be a smooth version of A:A

Variations can be
WA3 of B3 = (1*A1+2*A2+1*A3)/(1+2+1)
WA3 of B4 = (1*A2+2*A3+1*A4)/(1+2+1)

WA101, WA201 become difficult to be listed as above examples.
Excel array formulas confuses me soon, thank you for your help in advance !
 
B

Billy Liddel

H.C.

I don't think you need a weighted average, just smoothed data.

Using the data from your last post using B4:B10 use the formula

C5: =SUM(B4:B6)/COUNT(B4:B6)

Or using the Offset function from you last post

C5: =SUM(OFFSET($B5,0,0,3))/3

HTH

If this helps please tick box.

Peter Atherton
 
H

H.C. Chen

I tried a set of data as shown below. (Draw the chart) We can see "Average"
makes significant peaks of the data disappeared, while "Weighted Average" not
only smooth the curve but still keep peaks observable.

Data, Average , Weighted Average
1 , 2 , 1.142857143
3 , 4.333333333 , 3.571428571
9 , 4.666666667 , 7.142857143
2 , 5.333333333 , 3.428571429
5 , 4.333333333 , 4.714285714
6 , 7.666666667 , 6.714285714
12 , 9 , 10.71428571
9 , 9.666666667 , 9.285714286
8 , 7 , 7.571428571
4 , 6 , 4
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top