Average & Standard Deviation Loop

[smadsen99]

Hello to anyone who can help:

I am working on a research project and I was looking for some hlep in writing a program. What I want the program to do is to take the average and standard deviation of a varying set of values and then reject any values which fall beyond 1 standard deviation from the average. Ideally the program would retake the average and standard deviation and reject any values which fall beyond 1 standard deviation from this new average. I would like this to loop until the standard deviation is less than a certain number (ie: 0.5). I know excel already has functions to take averages and standard deviations but if someone knows of a program like this that exists or could give me some advice on how to go about this, it would be greatly appreciated.

Best,
Sean

[shg]

Does the attachment do conceptually what you want?

[smadsen99]

Conceeptually, yes, but would it be possible to write a function or something like this in VB? Ideally I'd like to be able to enter the formula in a cell, select the range of cells i'd like to apply it to, and have it output the final average.

[shg]

How would you specify the values for progressive culling -- what would the function signature look like?

[smadsen99]

I'm just beginning to learn VB in one of my classes so I'm not sure if this is possible, but something similar to the average function ie: =average(value1:valuen). Is that what you were asking?

[shg]

No.

A VBA function, like any worksheet function (save RAND, TODAY, ...), takes arguments.

What I want the program to do is to take the average and standard deviation of a varying set of values and then reject any values which fall beyond 1 standard deviation from the average.

Always 1?

Ideally the program would retake the average and standard deviation and reject any values which fall beyond 1 standard deviation from this new average.

Always 1?

I would like this to loop until the standard deviation is less than a certain number (ie: 0.5).

Always 0.5?

So the function rejects values with a deviation >1 until the standard deviation of the reduced sample (or population?) is less than 0.5?

[smadsen99]

Exactly. I would like it to keep runnig until the standard deviation of the reduced sample is less than 0.5.

And thanks for being patient with me, I really appreciate it.

[shg]

Please Login or Register  to view this content.

Col B is the input data. Cols D:H show solution by formula.

J7:J30 arrives at the same result with the single array formula (see http://www.cpearson.com/excel/ArrayFormulas.aspx)

=TRANSPOSE(x(C7:C30))

... where the function x is

Please Login or Register  to view this content.