Moving Average , Please Help

[sohoo]

Hi, i like to ask for this simple moving average formular, for SMA days formular
example

Data 3 (days) just change this day
1, 23
2. 45
3. 56
4. 24
5. 35
6. 78
7. 52
8. 34
9. 23
10.67

how can i set SMA can change the days like key in 3 (that mean 3days SMA) then if i change to 5 (5days SMA) can set in 1 column just replace the day then can get the SMA?

 

[Yard]

Hi, welcome.

I'm afraid your description is a little unclear to me, but if you mean:

You want to change the value of cell A1 to 3, and then B1 will reflect the average of the first 3 data values...

then try this with your data values starting in cell B2 :

=AVERAGE(OFFSET(B2,0,0,A1,1))

 

[diddi]

this is just a quick solution, but it seems to work ...

Sub CalcMove()
    Dim row As Integer, row2 As Integer, res As Integer, ave As Double
    With Sheets("My averages")
        res = InputBox("Pick your moving average base")
        For row = 1 To 10 - res
            For row2 = 0 To res - 1
                ave = ave + .Cells(row + row2, 1)
            Next row2
            .Cells(row, 2) = ave / res
            ave = 0
        Next row
    End With
End Sub