Conditional formatting to find 3 closest values

confusedjo

New Member
Joined
Aug 16, 2011
Messages
28
Im currently trying to use conditional formating to highlight a col of numbers (A1:A99) to find the closest 3 values to the value entered into a cell (B2), including a exact match and ties.

Any help would be great.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi confusedjo,

you could insert two auxiliary columns:
column C is the absolute delta of column A to B2, column D is the RANK of each element in column C.

Now you can add three conditional formats for column with the following formula (with cell A1 being the active cell): =$D1=1

HTH!
 
Upvote 0
If there are 5 closest values all the same you'll want those 5 cells highlighted, but will you want to highlight any more cells?
What version of Excel?
 
Upvote 0
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):
Excel Workbook
2
2
3
3
5
5
6
6
7
Sheet


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?
 
Upvote 0
Re first reply
Excel 2010 version

If there are 5 values (ties) then yes i would wont all highlighted.

Re second reply

I would want the list to look as it does - I only need the 3 closest values but if there are ties like you have in your example then it would be the 2 closest values.

examaple:-
scores:- 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33,34 and the value i want to find the 3 closest is 27 - then 27, 28, 26 would highlight.

Scores:- 23, 24, 25, 26, 27, 27, 28 , 29, 30, 31, 32, 33,34 and the value i want to find the 3 cloest is 27 - then 27, 27, 28 (or 26) would highlight.

Thank you for you time in responding and hope this makes sense.
 
Upvote 0
Hi confusedjo,

you could insert two auxiliary columns:
column C is the absolute delta of column A to B2, column D is the RANK of each element in column C.

Now you can add three conditional formats for column with the following formula (with cell A1 being the active cell): =$D1=1

HTH!

Sorry i dont really understand this (rather new to excel)
 
Upvote 0
Right, this is going to take some study!
Excel Workbook
ABCDE
12331323
224271424
3251625
4262627
5272727
6272727
7282827
8293927
9306027
10317031
11328032
12339033
133410034
Sheet
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =ISNUMBER(MATCH(ABS($B$2-A1),SMALL(ABS($B$2-$A$1:$A$13),diddy),0))Abc
C11. / 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)Abc
E11. / Formula is =ABS($B$2-E1)<=SMALL(ABS($B$2-$E$1:$E$13),$B$1)Abc


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.
 
Upvote 0
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.

Thanks heaps!
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,223,171
Messages
6,170,478
Members
452,332
Latest member
The2ndQuest

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top