How to make Weighted Average

[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 !

 

[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.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

 

[H.C. Chen]

Picture FYR
http://sites.google.com/site/guitardingdong/excel-memo/Excel_howto_WA.gif?attredirects=0

My point is : how to make Weighted-Average-of-100-cells ? 100 , 200 or more
....