Standard Deviation (Conditional!)

RobStach

New Member
Joined
Oct 27, 2002
Messages
2
Hello all ... this is my first post here and let me say that I absolutely love this message board. Everyone seems so helpful. Let me thank everyone in advance for all of your help. Ok ... here is my question/scenario.

I have a single column of data. From this data I am looking to find the Standard Deviation BUT I do not want to include any cells where the value is 1.5x the Average of the column (my average throws out min and max values).

I have scoured the posts and thought I had it on a couple of occasions but no luck yet.

Any ideas?

Rob
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Try the DSTDEV function. It is one of the built-in Database functions. In the example, the value 9 in column A is ignored because it is 1.5*The Average of the column (I did not exclude max and min values). THe yellow area is the criteria range for the D function. HTH
Book1
ABCD
1Num
25TRUE
36
47
541.322876
68
79
86
94
105
116
Sheet1

This message was edited by lenze on 2002-10-28 10:57
 

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
On 2002-10-28 10:34, RobStach wrote:
Hello all ... this is my first post here and let me say that I absolutely love this message board. Everyone seems so helpful. Let me thank everyone in advance for all of your help. Ok ... here is my question/scenario.

I have a single column of data. From this data I am looking to find the Standard Deviation BUT I do not want to include any cells where the value is 1.5x the Average of the column (my average throws out min and max values).

I have scoured the posts and thought I had it on a couple of occasions but no luck yet.

Any ideas?

Rob

Welcome to the board Rob,

Assuming you mean 1.5 Standard Deviations either side of the mean, the following Array formula will do the trick

My data is in cells A2:A10 and it works correctly on this...To enter an Array formula enter the formula excluding the Curly brackets and then Press Ctrl+Shift+Enter Excel will put the curvy brackets around.

=AVERAGE(IF(A2:A10<(STDEV(A2:A10)*1.5+AVERAGE(A2:A10)),IF(A2:A10>(AVERAGE(A2:A10)-STDEV(A2:A10)*1.5),A2:A10)))
:)
 

RobStach

New Member
Joined
Oct 27, 2002
Messages
2
Thank you for the quick replies Lenze and Sean. You guys are great!

Sean, you are getting me closer to where I need to be with this problem. The only difference I can see is that I already have my average in another cell. I dont want to include anything over 1.5x the Average within the STDEV formula, not 1.5 to either side of the mean.

For example: I have 5 cells A2:A6 -(5, 8, 7,6...and 56) The average is computed in A8 without the high/low (56 & 5). I want to do something similar with STDEV in A9 with the condition that anything over 1.5x the Average(A8)is exluded (10.5 in this example)which would give me STDEV on 5,6,7 and 8.

I have the averaging formula all worked out, that one was easy and I need to have a separate cell for it.

Lenze .. I will give your method a try next!
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Here's my example using your data and criteria. I just made up the formula in A8. You may have a more elegant one for the average.
Book1
ABCD
1Num
25TRUE
38
47
561.290994
656
7
810.5
Sheet1


Edit: Reposted html because formula did not come through
This message was edited by lenze on 2002-10-28 12:19
 

Forum statistics

Threads
1,143,708
Messages
5,720,388
Members
422,282
Latest member
psunith

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
Top