Additionally to the two questions in my last message, say you had to highlight the closest 2 values to the value 4 from this list (I realise the list might not be sorted in reality):
you might highlight the yellow cells (they're all 1 away from the target 4). Now you want the closest 3 values; would you stick with the yellow cells or include the orange cells (which are all 2 away from the target) too?
1. / Formula is =ISNUMBER(MATCH(ABS($B$2-A1),SMALL(ABS($B$2-$A$1:$A$13),diddy),0))
1. / Formula is =ABS($B$2-C1)<=SMALL(IF(FREQUENCY(ABS($B$2-$C$1:$C$13),ABS($B$2-$C$1:$C$13))>0,ABS($B$2-$C$1:$C$13)),$B$1)
1. / Formula is =ABS($B$2-E1)<=SMALL(ABS($B$2-$E$1:$E$13),$B$1)
There are three type of conditional formatting above, 1 each in columns A, C & E. They behave quite differently from one another.
I've used B2 as the target value for all three columns.
B1 is referred to in the conditional formatting for columns C and E only, and is the x in 'highlight the nearest x'.
Column E first:
This is the simplest, but has limitations when there are lots of ties, because only the nearest 1 value will be highligted if there are more than 3 ties nearest. Play with the values in B1 and B2.
B1 isn't used in this formula. It uses a defined Name (diddy) which is defined as:
because conditional formatting didn't like using hard-coded arrays.
Actually, this one might be quite similar to column E.
This one's my favourite; it first strips out duplicates from the list to get a unique list, then takes the three closest values and highlights them, regardless of how many there might be. It will include values which are equidistant from the target, above or below, so may seem to highlight quite a lot when there are such equidistant values. It does use both B1 and B2 - so play with these values.
I couldn't easily find a way to get the results exactly as you want in your last message.
WOW! (is my first comment) thank you so much for your work.
I will try all the suggested forumla's to see which works best, will leave message when i have find which appears to be the one that suits my list of scores.
Column C worked great thanks - I played with the values as you suggested and it appears having a 2 in B1 gives a closer result to what i was wanting.
Thank you so much, this is going to help me out so much!!