Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make Weighted Average
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 ! -- 事情不耐做,問題不耐看。 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make Weighted Average
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" wrote: 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 ! -- 事情不耐做,問題不耐看。 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make Weighted Average
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 -- 事情不耐做,問題不耐看。 "Billy Liddel" wrote: 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" wrote: 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 ! -- 事情不耐做,問題不耐看。 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make Weighted Average
Picture FYR http://sites.google.com/site/guitard...attredirects=0 My point is : how to make Weighted-Average-of-100-cells ? 100 , 200 or more .... -- 事情不耐做,問題不耐看。 "H.C. Chen" wrote: 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 -- 事情不耐做,問題不耐看。 "Billy Liddel" wrote: 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" wrote: 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 ! -- 事情不耐做,問題不耐看。 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Weighted Average | Excel Discussion (Misc queries) | |||
Weighted average | Excel Worksheet Functions | |||
Weighted Average | Excel Worksheet Functions | |||
Weighted Average | Excel Discussion (Misc queries) | |||
Non zero weighted average | Excel Worksheet Functions |