Standard Deviation (Conditional!)

[RobStach]

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

 

[lenze]

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
	A	B	C	D
1	Num
2	5		TRUE
3	6
4	7
5	4		1.322876
6	8
7	9
8	6
9	4
10	5
11	6
Sheet1

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

 

[s-o-s]

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]

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]

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
	A	B	C	D
1	Num
2	5		TRUE
3	8
4	7
5	6		1.290994
6	56
7
8	10.5
Sheet1

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