How to include values from a serie within a given coefficient of variation

[Tonio]

Hi,

I have a serie of values (cf. bellow) from which I want to sort as following:

The coefficient of variation (CV) (=Stdev/mean) of this population should be 5% or bellow. As you might see from the whole population, the CV is higher than 5%. How can I keep the population the biggest possible with a CV of 5% or less and exclude the values which increase the CV above 5%. Is there a formula I could apply ? Thereafter, it could be nice if the excluded cells would automatically be detectable. Thanks for your help, regards, Tonio.

1.338 1.298 1.390 1.406
1.471 1.466 1.485 1.496
1.551 1.258 1.529 1.514
0.790 1.547 1.514 1.586
1.546 1.565 1.523 1.585
1.500 1.506 1.511 1.533
1.507 1.521 1.586 1.596
1.509 0.698 1.486 1.583
1.486 1.589 1.487 1.633
1.466 1.516 1.517 1.543
1.518 1.555 1.559 1.586
1.295 1.470 1.474 1.575
1.425 1.513 1.547 1.606
1.462 1.443 1.477 1.503
1.344 1.386 1.484 1.529
1.250 1.317 1.354 1.407

 

[pgc01]

Hi Tonio
Welcome to the board

I don't think you can do it with one formula. If I understand correctly this is an iterative process. You can implement a table with the values resulting from each step and use it to flag the numbers to be eliminated using conditional formatting.

This is how I'd do it:

- detect which cell to eliminate in order to make the CV smaller. You can choose the cell farthest away from the mean.
- eliminate this number and calculate the new mean, std and CV
- repeat until the CV is less than the target (in your example 5%)

If you know vba you could also code a udf or a macro to perform the detection of the cells.


If you try the method I suggested but have difficulties,
- explain the context of the problem, what does it refer to
- what you've done and where you stopped.
- post your version of excel