Forum Discussion
Solved
Selecting Data with Minimum Standard Deviation
Hi everyone, I'm working on a project but the data collected is more than needed, so I need to choose data with the minimum deviation or least error. Example : Thickness of material A with 10x ...
In the attached version, I have implemented my VBA algorithm (with StDev_P instead of StDev_S) as a custom function. It is used in an array formula confirmed with Ctrl+Shift+Enter to return the selected values.
mtarler wrote: ``we know std will be lowest for numbers close together``
My version of Excel does not support those features.
Which subset of 5 is chosen with the following set of data:
1.1
1.3
1.5
1.7
1.9
100.1
100.2
100.3
100.4
100.5
The correct subset is 100.1 through 100.5 (sd = 0.141421356237312), not 1.1 through 1.9 (sd = 0.282842712474619).
And just for fun, which subset is chosen if the first 5 numbers are 1.1, 1.2, 1.3, 1.4 and 1.5?
In that case, the sd is the same.
my comment was purely to explain why I sort the numbers first and can then increment through that sorted list instead of checking every combination. i.e. only check N-5 (i.e. 5) instead of 252 cases.
As for the answers, it pointed out an error in my formula (i.e. it was offset by 1 and didn't check the first entry correctly) so I edited the above post to have the correction.
That said, my formula finds the first set with the minimum SD while Hans' find a correct set but not necessarily consistent which.
As for the answers, it pointed out an error in my formula (i.e. it was offset by 1 and didn't check the first entry correctly) so I edited the above post to have the correction.
That said, my formula finds the first set with the minimum SD while Hans' find a correct set but not necessarily consistent which.