Quick Standard Deviation question

M

mlarson

Well-known Member
Joined
Aug 25, 2011
Messages
509
Office Version
  1. 2010
Platform
  1. Windows
This is probably very easy but I don't know the formula. I want to take the standard deviation of the 5 values closest to the bottom of column H and have the output in cell U4. And the 5 closest to the bottom of column I with output in cell V4. And the 5 from column J to cell W4.

Do you know of a simple formulas to do this? My spreadsheet will be changed regularly so the bottom data will constantly be changing and I need to always capture the bottom 5 values. Thanks.

Here's the data and desired output.

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">H</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">I</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">J</td> </tr> <tr style="height: 18.75pt;" height="25"> <td class="xl63" style="height: 18.75pt; border-top: medium none;" align="right" height="25">0.000341</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.001327</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.001669</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.009259</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00841</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.000853</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.006787</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.003503</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.01029</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.000312</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00054</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00023</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.00143</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00316</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00459</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.00431</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.007559</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.003244</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.00112</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00327</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00439</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.00279</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00062</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.002173</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.01296</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.020307</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.007348</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.00372</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.016333</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.012608</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.002656</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.006031</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.008687</td> </tr> <tr style="height: 18.75pt;" height="25"> <td class="xl63" style="height: 18.75pt; border-top: medium none;" align="right" height="25">0.005417</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00643</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00101</td> </tr> </tbody></table>
Desired Output
Cell U4: 0.00740
Cell V4: 0.01121
Cell W4: 0.00536
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
mlarson said:
This is probably very easy but I don't know the formula. I want to take the standard deviation of the 5 values closest to the bottom of column H and have the output in cell U4. And the 5 closest to the bottom of column I with output in cell V4. And the 5 from column J to cell W4.

Do you know of a simple formulas to do this? My spreadsheet will be changed regularly so the bottom data will constantly be changing and I need to always capture the bottom 5 values. Thanks.

Here's the data and desired output.

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">H</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">I</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">J</td> </tr> <tr style="height: 18.75pt;" height="25"> <td class="xl63" style="height: 18.75pt; border-top: medium none;" align="right" height="25">0.000341</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.001327</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.001669</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.009259</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00841</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.000853</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.006787</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.003503</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.01029</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.000312</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00054</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00023</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.00143</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00316</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00459</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.00431</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.007559</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.003244</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.00112</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00327</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00439</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.00279</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00062</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.002173</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.01296</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.020307</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.007348</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">-0.00372</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.016333</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.012608</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="20">0.002656</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.006031</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">0.008687</td> </tr> <tr style="height: 18.75pt;" height="25"> <td class="xl63" style="height: 18.75pt; border-top: medium none;" align="right" height="25">0.005417</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00643</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">-0.00101</td> </tr> </tbody></table>
Desired Output
Cell U4: 0.00740
Cell V4: 0.01121
Cell W4: 0.00536
Click to expand...
Try...

=STDEV(OFFSET(INDEX(H:H,MATCH(9.99999999999999E+307,H:H)),0,0,-5))
 
Upvote 0
sure, for example:

data is in column h

make a macro like this so that it puts the row # of the last data value in G1

Code: 
Option Explicit

Sub atomic()
Dim i As Double
i = Range("H50000").End(xlUp).Row
Range("G1").Value = i
End Sub


then use your stdev with that value and -5, like:

Code: 
=STDEV(INDIRECT(ADDRESS(G1,8)&":"&ADDRESS(G1-5,8)))


so in this example I had data in H1:H9 and it only used H9:H4


to make it so you don't have to run the macro every time, set it to an event such as a cell value change (since you said your data will be changing frequently.
 
Upvote 0
I used the OFFSET...thanks again for providing it Aladin.
 
Upvote 0
Aladin, do you know how I would adjust your formula to have the last 5 non-blank cells (instead of just last 5 cells)? Thanks.
 
Upvote 0
mlarson said:
Aladin, do you know how I would adjust your formula to have the last 5 non-blank cells (instead of just last 5 cells)? Thanks.
Click to expand...

In reference to a static range...

1) Control+shift+enter, not just enter:
Rich (BB code): 
=STDEV(IF(ROW(H2:H13)>=LARGE(IF(ISNUMBER(H2:H13),
  ROW(H2:H13)),5),IF(ISNUMBER(H2:H13),H2:H13)))

In reference to a dynamic range...

Define BigNum as referring to:

=9.99999999999999E+307

Define Hrange as referring to:
Rich (BB code): 
=Sheet1!$H$2:INDEX(Sheet1!$H:$H,MATCH(BigNum,Sheet1!$H:$H))

With the latter definition at hand...

2) Control+shift+enter, not just enter:
Rich (BB code): 
=STDEV(IF(ROW(Hrange)>=LARGE(IF(ISNUMBER(Hrange),
  ROW(Hrange)),5),IF(ISNUMBER(Hrange),Hrange)))

If List/Table feature can be used, running (1) would suffice for the feature would allow the range referred to by the formula to adjust.
 
Upvote 0
Aladin, thank you for that. I plan on trying it out in the next few days and I'll give you feedback. Thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
228
kvsrinivasamurthy
K
T
  • Question Question
Exporting Cell Range As PNG - Getting A White Border To The Bottom & Right - Why?
2
Replies
16
Views
175
jolivanes
jolivanes
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
113
6StringJazzer
6StringJazzer
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top