Standard Deviation of Log Changes

[mlarson]

Hello, I have a question regarding standard deviation and log change. I have a large number of figures in Column A (more than 100,000). I would like to calculate the standard deviation of the log change for the 20 most recent entries (most recent entries entered at bottom of spreadsheet).

The log change includes the current figure in Column A and the figure immediately preceding the current figure. For example, log change in cell B3 would be LN(A3/A2).

What I want to do is avoid having the LN function in 100’s of thousands of cells, I would prefer to get the standard deviation of the 20 most recent LN functions and have that result displayed in one cell.

Is this possible? If so, I would greatly appreciate your input! Thanks, Mark


<table class="MsoNormalTable" style="width: 151pt; margin-left: 4.65pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="201"> <tbody><tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap"> Column A
</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap"> Column B (log change)
</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

161.57​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">
</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

163.30​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0107​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

163.02​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0017​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

157.47​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0346​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.99​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0096​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

156.98​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0127​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

154.48​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0161​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

156.67​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0141​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.01​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0085​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.71​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0044​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

161.28​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0161​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

157.10​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0263​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

154.54​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0164​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

155.65​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0072​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

162.84​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0452​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

155.04​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0491​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

150.56​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0293​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

152.15​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0105​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

156.83​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0303​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.32​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0095​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.00​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0020​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.10​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0006​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

159.00​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0057​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

157.75​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0079​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.40​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0041​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

161.57​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0198​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

163.30​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0107​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

163.02​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0017​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

157.47​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0346​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.99​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0096​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

156.98​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0127​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

154.48​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0161​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

156.67​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0141​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.01​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0085​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.71​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0044​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

161.28​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0161​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

157.10​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0263​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

154.54​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0164​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

155.65​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0072​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

162.84​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0452​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

155.04​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0491​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

150.56​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0293​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

152.15​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0105​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

156.83​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0303​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.32​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0095​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.00​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0020​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.10​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0006​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

159.00​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0057​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

157.75​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0079​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.40​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0041​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

161.57​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0198​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

163.30​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0107​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

163.02​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0017​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

157.47​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

-0.0346​

</td> </tr> <tr style="height: 15pt;"> <td style="width: 68.25pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="91" nowrap="nowrap">

158.99​

</td> <td style="width: 82.75pt; padding: 0in 5.4pt; height: 15pt;" valign="bottom" width="110" nowrap="nowrap">

0.0096​

</td> </tr> </tbody></table>

 

[shg]

In C2,

=MATCH(9E+307, A:A)

In another cell, confirmed with Ctrl+Shift+Enter rather than just Enter,

=STDEV(LN(INDEX(A:A, C2-19):INDEX(A:A, C2) / INDEX(A:A, C2-20):INDEX(A:A, C2-1)))

 

[mlarson]

Thank you very much!

 

[shg]

You're welcome, glad it helped.