I have a spreadsheet of measured data, the columns being mean and +/-3 sigma, these columns are against the measurement point, there are up to 20 of these columns as they are sets of data against a specific variant. I want to get excel to examine the columns against each measurement point and give me the best 4 measurements ie lowest mean and lowest range but they have to be the same variant ie if one column is 3 +/-1 and another is 2 +/-1 the analysis needs to show the 2 +/-1 is better.

Feel free to ask questions as I’m not sure I have explained it well enough.

Tricky to do in that native structure. Sounds like youre looking for the lowest 4 ranked Minimums of 20x Mean -variant in each row.

If Ive got that right, you’ll need to do it in stages.

Start with a new sheet that gives the Mean-variant for each of the 20 observations along the row (and then as far down as neccesary).

Then at the far right hand side of your ranges, use =SMALL(A1:T1, 1) to return the smallest, or second smallest etc.

That’s fine but I need to calculate the best combination of mean and range by variant at each measurement point, a sample set of data could be 3.34+/-0.67,4.32+/-1.59,0.02+/-0.12,3.00+/-1.66,5.35+/-2.12,0.40+/-0.51,6.22+/-2.64

How do you use Excel to calculate the 4 best mean and range combinations?

define “best” for me?

is it mean-variant?

Best is a combination of lowest mean with lowest variation, from the list above 3.34+/-0.67 is better than 3.00+/-1.66, better is the overall spread is tighter and closer to 0, difficult to describe properly however for example 5+/-1 is worse than 3.00+/-1.66

can you define numerically how you rank the relative bestness of one measure that is low but wide versus another that is not so low but narrow, because it looks subjective at the moment…

not sure if this is what you’re looking for, but its fairly easy to ‘rank’ sets of data from say ‘best’ to ‘worst’.

First of all insert the relevant formulas into the data sheet to return the results. Then to analyze the results, highlight the results data sheet go into format and choose conditional formatting. You can then insert the conditions to suit your own variables. For instance cells that return a ‘bad’ result can be formatted to be coloured red for example. Those that return a ‘good’ result can be formatted to be coloured green. This is applied to all the data that is highlighted so at a glance it can be seen all cells that are green are the ‘best’ results etc.

Apologies if this is way off the mark but that is how I would go about analysing a set of results as you can set your own criteria for what the results mean.