Easy Moving Average question

[mlarson]

I would like a code that would produce the moving average of the 4 most recent values. My actual datasheet is extremely large which makes using a simple formula and copying/pasting it rather tedious, so I am looking for a code that will perform this task.

Beginning with cell C4, the formula =Average(B1:B4) is used. Average is not used to produce the result in C1 - C3 because there doesn't exist 4 values to average.

Is there a code to produce these results (as opposed to a formula that needs to be copied/pasted)?

Here's sample data:
<table border="0" cellpadding="0" cellspacing="0" width="238"><col style="width: 48pt;" width="64"> <col style="width: 53pt;" width="71"> <col style="width: 77pt;" width="103"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl67" style="border-left: medium none; width: 53pt;" width="71">B</td> <td class="xl67" style="border-left: medium none; width: 77pt;" width="103">C</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">1</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">2</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">124.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">124.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">3</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">126.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">126.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">4</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.50 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.13 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">5</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">126.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.38 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">6</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">127.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">126.13 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">7</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">128.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">126.63 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">8</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">126.50 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">9</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">123.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.75 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">10</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.50 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.38 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">11</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">126.30 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">124.95 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">12</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">124.95 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">13</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.45 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">14</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">124.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.08 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">15</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">126.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.00 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">16</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.50 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.13 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">17</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">126.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.38 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">18</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">127.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">126.13 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">19</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">128.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">126.63 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">20</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">126.50 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">21</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">123.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.75 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">22</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.50 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.38 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">23</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">126.30 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">124.95 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">24</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">125.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">124.95 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">25</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">124.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.20 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">26</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">127.00 </td> <td class="xl67" style="border-top: medium none; border-left: medium none;">125.58 </td> </tr> </tbody></table>
<table border="0" cellpadding="0" cellspacing="0" width="238"><tbody><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; width: 48pt;" width="64" height="20">
</td><td class="xl68" style="border-left: medium none; width: 53pt;" width="71">
</td><td class="xl67" style="border-left: medium none; width: 77pt;" width="103">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">
</td><td class="xl68" style="border-top: medium none; border-left: medium none;">
</td><td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG08Sep18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, -1) < 4 [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 1) = Dn
    [COLOR="Navy"]Else[/COLOR]
        Dn.Offset(, 1) = Format(Application.Average(Dn.Offset(-3).Resize(4).Value), "0.00")
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mlarson]

MickG, that is perfect! Thank you! One follow-up question: how would I calculate the standard deviation of all values in column C (the output values from the code you just provided), with the answer being placed in cell D1? Thanks again!

 

[mlarson]

MickG, nevermind. I think I need more sleep as the answer is obviously =stdev(C1:C26).