#### sohoo

##### New Member
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?

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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))

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

Code:
``````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``````

Replies
10
Views
485
Replies
1
Views
700
Replies
3
Views
148
Replies
2
Views
348
Replies
0
Views
120

### Forum statistics

1,203,728
Messages
6,057,012
Members
444,902
Latest member
ExerciseInFutility

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back