Macros to create moving standard deviation calculation

[ewibolo]

Excel 2007

Hi, I am trying to use Macros to calculate the standard deviation of the last 5 cells I input for example:

<table width="44" border="0" cellpadding="0" cellspacing="0" height="207"><colgroup><col style="width:51pt" width="68"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:51pt" width="68" height="20">1356</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:51pt" width="68" height="20">4530</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:51pt" width="68" height="20">4450</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl64" style="height:15.75pt;width:51pt" width="68" height="21">5300</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:51pt" width="68" height="20">2960</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:51pt" width="68" height="20">4300</td> </tr> <tr style="mso-height-source:userset;height:15.75pt" height="21"> <td class="xl64" style="height:15.75pt;width:51pt" width="68" height="21">4530</td> </tr> <tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl64" style="height:18.75pt;width:51pt" width="68" height="25">5450</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:51pt" width="68" height="20">1300</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:51pt" width="68" height="20">2960</td> </tr> </tbody></table>
I will be adding more cells to the bottom of this list very often, and I would like to use macros to automatically calculate the standard deviation of the last 5 values.

Thanks for your help in advance!

Ewi

 

[shg]

Welcome to the board. How about just a formula?

=STDEV(INDEX(A:A, MATCH(9E+307, A:A)):INDEX(A:A, MATCH(9E+307, A:A)-4))

 

[ewibolo]

Holy Mackerel! Thanks very much!

Have a nice day,

Eric

P.S. - thanks for the warm welcome

 

[ewibolo]

how about if I have a column broken into sections (ex.- 100 rows divided into 25)

A1-A25
A26-A50
A50-A75
A75-A100

I am constantly updating each section by adding one or two entries per day to each section.

That formula works perfectly,but if I only have 1 type of item and am using the entire column for the calculation.

How about if I have one column broken up into 4 different sections and would like to calculate the standard deviation for each of the 4 sections in that column?

Don't let the evenly divided sections mislead you.....they are all different in my spreadsheet. One column may have 35 entries, the other may have 27, 14, etc. I update and add them individually and not evenly, so that the next day I might have 38 entries,27,15, etc.


Thanks for the help!