Conditional formatting: show me all values that are not more than x% appart from each othe

[SMLlab]

Hello,
I got a column with values like

12
13
16
12
9
8
2
15
1
2
15
16
5
8
6

Goal: show all the 12s, 13s bunched together in one color, all the 15s ,16s in a different color and all the 8s and 9s, too - basically all the numbers that are say 15% from each other or 1 point apart (12, 13 etc). And if they are 2 points apart (13, 14, 15, 16) they should be grouped 13, 14 and 15, 16 rather than 14, 15 with 12 and 16 left out.

Is this possible?

Thank you
SMLlab

[6StringJazzer]

It's not possible because the specification for what you want is not sufficiently rigorous.

And if they are 2 points apart (13, 14, 15, 16) they should be grouped 13, 14 and 15, 16 rather than 14, 15 with 12 and 16 left out.

This cannot be interpreted as a rule. 13 and 14 are 1 point apart. 14 and 15 are one point apart. 15 and 16 are one point apart. So they would all get the same color. I do not know why you say they are 2 points apart.

What is the rule that says that 13 and 14 should be together, and 15 and 16 to be together, but not 14 and 15? Why not? You mentioned 12 but I have a feeling you meant to say 13.

When numbers get larger, 15% gets bigger. The numbers 100 and 114 are within 15%. What would you do with a sequence like 100, 102, 105, 110, 114?

You either need to develop mathematical rules that are more mathematical, or tell us all about the purpose of why you are doing this so we can help you figure it out.

[SMLlab]

Hello Jazzer,
thank you for your reply. Yes, I mean 13 and not 12.

Those numbers are tracer tracee ratios in blood levels which will only range between 1 to 20. Unfortunately the method we are using knocks the values so much down that we are creeping somewhere in the just detectable range. If tracer or tracee jumps up due to the limitations of the method (variation in the blood plasma preparation) a small change will slap the ratio out of wack and instead of a ratio of 15% we will also get ratios like 2% or 8%. To minimize those method variations might not be possible. Since we do several measurements we can say which one is the correct but we tend towards the higher ratio values like 15 or 12. To check which one is the correct our approach now is to count which one occurs more. Since we tolerate to bundle 14 and 15 we can also bundle 12 and 13 (or 11) but we have to make a rule where not to bundle anymore. Thats why I say 14 and 15 get bundled but not 13 (2 points difference) - I know that is somehow difficult to determine where we cut off.
I hope that makes the question more clear.

sorry, I can't attach the excel file itself since it has patient data and is crazily cross linked

best
SMLlab

[6StringJazzer]

I understand your specific example but I just cannot see a rule that could be applied to any numbers between 1-20.

we can also bundle 12 and 13 (or 11)

That is not a rule that you can express mathematically. "or 11" leaves doubt about what to do.