Forum Discussion
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 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
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.
- Can you attach a sample file with some of your data (anonymized)?
Here is a brute force approach for your specific situation. It uses VBA, so you'll have to allow macros.
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``)
Thanks. It's easy to modify the macro - I'll leave that to the OP.
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.)
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
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.5The 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.
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)
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.
- 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) 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.
