Right, this is going to take some study!
Excel Workbook |
---|
|
---|
| A | B | C | D | E |
---|
1 | 23 | 3 | 13 | | 23 |
---|
2 | 24 | 27 | 14 | | 24 |
---|
3 | 25 | | 16 | | 25 |
---|
4 | 26 | | 26 | | 27 |
---|
5 | 27 | | 27 | | 27 |
---|
6 | 27 | | 27 | | 27 |
---|
7 | 28 | | 28 | | 27 |
---|
8 | 29 | | 39 | | 27 |
---|
9 | 30 | | 60 | | 27 |
---|
10 | 31 | | 70 | | 31 |
---|
11 | 32 | | 80 | | 32 |
---|
12 | 33 | | 90 | | 33 |
---|
13 | 34 | | 100 | | 34 |
---|
|
---|
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.
Column A:
B1 isn't used in this formula. It uses a defined Name (
diddy) which is defined as:
={1,2,3}
because conditional formatting didn't like using hard-coded arrays.
Actually, this one might be quite similar to column E.
Column C:
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.