Selecting Data with Minimum Standard Deviation

[alvaro037]

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 measurement is 

A  = 8.7  8.9  9.0  9.5  8.4  8.1  8.6  8.7  8.9  9.1

From this I need to select 5 of them with minimum deviation, its AVG and show the data selected. Is there any formula for the selection or any approaches should I take?

Thank you for the attention

[#2]

Can you attach a sample file with some of your data (anonymized)?

[#3]

@alvaro037 

Here is a brute force approach for your specific situation. It uses VBA, so you'll have to allow macros.

[#4]

@HansVogelaar 

Use StDev_P and STDEV.P (STDEVP), not StDev_S and STDEV.S (STDEV).

StDev_P returns the actual std dev of a set of numbers.

Use StDev_S when the set of numbers represents a sampling from a larger population, and we want to estimate the std dev of the population, not the subset per se.

That does not apply here (``choose data with the minimum deviation``)

[#5]

@JoeUser2004 

Thanks. It's easy to modify the macro - I'll leave that to the OP.

[#6]

@alvaro037 

We might note that @HansVogelaar's algorithm always finds (one of) the optimal solutions.  And it is quick because COMBIN(10,5) is only 252.

In contrast, the equivalent Solver model does not find the best solution if we use the GRG Nonlinear method.  The Evolutionary (random) method does stumble upon (one of) the optimal solution.  But it takes a long time (although we can change that using Options).

OTOH, the benefits of using Solver is:  (a) it does not require VBA coding; and (b) it adapts easily to different subset sizes.

(We could easily modify @HansVogelaar's algorithm to adapt to different "population" sizes.)

[#7]

@alvaro037  Here is an alternate solution.  I reduce the number of trials because we know std will be lowest for numbers close together so I sort the numbers and then increment through the list:

=LET(in,B2:K2,
n,5, 
sortin, SORT(TRANSPOSE(in)), 
start, SEQUENCE(ROWS(sortin)-n),
stdlist, SCAN(0,start,LAMBDA(p,i,STDEV.P(INDEX(sortin,start+i-1)))),
findLow,MATCH(MIN(stdlist),stdlist,0),
INDEX(sortin,start+findLow-1))

see attached

EDIT:

a) please note my SD calculation used STD.P per Joe's suggestion above while Hans' uses STD.S and my solution found a set starting with 8.6-8.9 and Hans' found 8.7-9.0 but the SD is the same in each case.

b) here is an alternate version of the formula that stacks/includes the AVG and SD:

=LET(in,B2:K2,
n,5,
sortin, SORT(TRANSPOSE(in)),
start, SEQUENCE(ROWS(sortin)-n),
stdlist, SCAN(0,start,LAMBDA(p,i,STDEV.P(INDEX(sortin,start+i-1)))),
findLow,MATCH(MIN(stdlist),stdlist,0),
ans,INDEX(sortin,start+findLow-1),
out,VSTACK(ans,"Avg",AVERAGE(ans),"SD",STDEV.P(ans)),
out)

I prefer to have them separate as in the attached file but that is up to you

[#8]

@alvaro037 

Re:  Standard Deviation - a manual method

The data was sorted and the end cells were eliminated to reduce the data to five cells.

Only takes two tries...

'---

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

[#9]

@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.

[#10]

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.

[#11]

Thanks everyone @JKPieterse  @HansVogelaar  @JoeUser2004 @mtarler  @Nothing_Left_to_Lose 

I've attached the data I have below. I like the approaches of @mtarler and I think it would suits best with my data. I'm not familiar in anyway in VBA so I don't know how to replicate this for multiple dataset. The attached file have what I imagine easily useable.

Data Set Min SD.xlsx

[#12]

I tried to paste formula but I think there is problem because of me using english terms for the functions and don't know the correct translation.
2 changes are
a) change the range for 'in' to be row 3
b) using the 2nd formula above that has both make the last line: TRANSPOSE(out)

[#13 · Accepted solution]

@alvaro037 

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.

[#14]

@mtarler  wrote:

=LET(in,B2:K2,
n,5,
sortin, SORT(TRANSPOSE(in)),
start, SEQUENCE(ROWS(sortin)-n),
stdlist, SCAN(0,start,LAMBDA(p,i,STDEV.P(INDEX(sortin,start+i-1)))),
findLow,MATCH(MIN(stdlist),stdlist,0),
ans,INDEX(sortin,start+findLow-1),
out,VSTACK(ans,"Avg",AVERAGE(ans),"SD",STDEV.P(ans)),out)

Forgive, but I don't speak the new Excel 365 language.

Please describe in one or two sentences what the code above does.

For example, does it find the contiguous n sorted data with the smallest std dev?

The operative word is "contiguous".  Right?

[#15]

@JoeUser2004  sure. and ugh I found another error in going through it because there were 10 items and looking for 5 I was using the n=5 and the 10-5 (i.e. 5) together.  I tweaked the code to account for possible other combinations:

=LET(in,B3:K3,
n,5,
sortin, SORT(TRANSPOSE(in)),
start, SEQUENCE(ROWS(sortin)-n+1),
stdlist, SCAN(0,start,LAMBDA(p,i,STDEV.P(INDEX(sortin,SEQUENCE(n,,0)+i)))),
findLow,MATCH(MIN(stdlist),stdlist,0),
ans,INDEX(sortin,SEQUENCE(n,,0)+findLow),
out,VSTACK(ans,AVERAGE(ans),STDEV.P(ans)),
TRANSPOSE(out))

line 1: define the input range

line 2: define how many items to select

line 3: rearrange the data from in to be sorted in order

line 4: define a sequence 1,2,3,... up to the length of the input minus N so if you have 100 inputs and selecting 5 then you will want to start at 1,2,3,..., 93,94,95,96 and you stop at 96 because after that you won't have enough left to get 5 items

line 5: create a list of standard deviations for each group of n items starting with index 1 and going to the end of the list

line 6: find the location in the list where the MIN value is located

line 7: create a list of the N values used to create that MIN standard deviation value

line 8: stack the list from 7 and add the average and the standard deviation

@alvaro037  please see this formula or the attached file for the corrected formula

[#16]

@mtarler  wrote:

``you will want to start at 1,2,3,..., 93,94,95,96 and you stop at 96 because after that you won't have enough left to get 5 items

create a list of standard deviations for each group of n items starting with index 1 and going to the end of the list

find the location in the list where the MIN value is located``

So again, in simple terms, looking at the sorted list of data, you find the 5 contiguous data with the minimum std dev.  Contiguous means that the sets of data are items {1,2,3,4,5}, {2,3,4,5,6}, {3,4,5,6,7}, etc, {94,95,96,97,98}, {95,96,97,98,99} and {96,97,98,99,100}.

Is that right?

[#17]

correct