Finding Nearest 3 Values Whether Higher Or Lower

[Pejayuk]

Hi, I would really appreciate help with finding a solution for the following.

I have a list of data contained in columns A-K. This data cannot be sorted and I need to find the 3 nearest values to the value I select from column F.

I've been trying to achieve this by using {=MIN(IF(O3:O30>L2,O3:O30))} and {=MAX(IF(O3:O30<L2,O3:O30))}. (I'm only using 30 rows in this test). If I repeat these formulas a few times, it does give roughly the correct results, but also some I don't want.

It doesn't work at all if I select the lowest or highest value. For example: If I test it with values 10 - 300 increasing by 10 per row. If I select 10, because there is no lower value, I would like it to find the three closest higher values. Likewise, if I select 300, I would like to display 290, 280, 270 in that order. Another problem is, the actual data in column F will have a minimal number of duplicates.

Thanks in advance for any suggestions.

 

[jasonb75]

Another problem is, the actual data in column F will have a minimal number of duplicates.

If there is a duplicate in the nearest 3 values, should there be a duplicate in the results, or should it be 3 unique values?

Are the values all positive or could there be negative / zero values in the data?

Are they all integers or are there decimal values? If so, is rounding needed?

 

[jasonb75]

Trying a few things out, this seems to work with data in your test range.

In L3 and fill down to L5

=AGGREGATE(15,6,SMALL($O$3:$O$30,COUNTIF($O$3:$O$30,"<"&$L$2)-3+{1,2,3,4,5,6})/ISNUMBER(MATCH(ABS(SMALL($O$3:$O$30,COUNTIF($O$3:$O$30,"<"&$L$2)-3+{1,2,3,4,5,6})-$L$2),AGGREGATE(15,6,ABS(SMALL($O$3:$O$30,COUNTIF($O$3:$O$30,"<"&$L$2)-3+{1,2,3,4,5,6})-$L$2),{1,2,3}),0)),ROWS(L$3:L3))

As far as I can see, it works with positive, negative and decimal values with no issues. The potential fail point will be duplicates and tiebreakers.

Using a simple example, comparing 5 to 2,4,6,8 should the results be 2,4,6 or 4,6,8?
Also, comparing 5 to 2,4,6,6,8 should the results be 4,6,6 or 2,4,6 or 4,6,8?

 

[Pejayuk]

Thanks for your help. I can see I didn't provide enough information.

If there is a duplicate in the nearest 3 values, I would like the duplicates displayed in the results.

All the values are positive whole numbers that require no rounding. Also no zero values.

Comparing 5 to 2,4,6,8. Though it is not critical whether it shows 2,4,6 or 4,6,8 as this is not likely to happen often at all with the real data. The best result would be if both the third highest and lowest numbers could be displayed. (So in this instance returning 4 results instead of the normal 3).

Comparing 5 to 2,4,6,6,8 the result I would need is 4,6,6.

Will try your formula tomorrow and post back how it does.

Thanks again.

 

[jasonb75]

Comparing 5 to 2,4,6,8. Though it is not critical whether it shows 2,4,6 or 4,6,8 as this is not likely to happen often at all with the real data. The best result would be if both the third highest and lowest numbers could be displayed. (So in this instance returning 4 results instead of the normal 3).

Filling the formula down more rows will return up to 6 values without any changes (max of 3 values less than L2 and 3 values greater than or equal to L2). If there are no duplicates then only the 3 closest values will be shown, the rest will return #NUM errors, so it will just need IFERROR to tidy things up a bit.

A maximum of 4 rows will be needed for duplicate values, or for situations like 5 from 2,4,6,8. If there is possibility of triplicate values then filling down 6 rows should give the correct results, however this is the limit of the formula due to the array method used. Going beyond this will need changes to the arrays which would make it less efficient.

=IFERROR(AGGREGATE(15,6,SMALL($O$3:$O$30,COUNTIF($O$3:$O$30,"<"&$L$2)-3+{1,2,3,4,5,6})/ISNUMBER(MATCH(ABS(SMALL($O$3:$O$30,COUNTIF($O$3:$O$30,"<"&$L$2)-3+{1,2,3,4,5,6})-$L$2),AGGREGATE(15,6,ABS(SMALL($O$3:$O$30,COUNTIF($O$3:$O$30,"<"&$L$2)-3+{1,2,3,4,5,6})-$L$2),{1,2,3}),0)),ROWS(L$12:L12)),"")

 

[Pejayuk]

I've been testing both formulas and I'm having one or two problems. The major one is that I couldn't get it to work at all on the desktop pc I use for this data. I've discovered the version of Excel on there doesn't have the AGGREGATE or IFERROR functions.

Trying it on a more up-to-date version on my laptop, it works pretty much as I'd hoped. The only thing is, it includes the target number in the three nearest results. For example: Using values of 1-10. If I enter 4 in L2 it returns 3,4,5 where I was looking for 3,5 and either 2 or 6 or both. (Rather than the same value I entered because I already have this value).

Just one other question if I may, does it matter if I change the formula to search column F instead of O or is the formula already doing something with reference to column F?

Thanks again for your help.

 

[jasonb75]

I've been testing both formulas and I'm having one or two problems. The major one is that I couldn't get it to work at all on the desktop pc I use for this data. I've discovered the version of Excel on there doesn't have the AGGREGATE or IFERROR functions.

I'll give it some thought but I'm not sure that it will be possible with such an old version of excel, there are too many useful functions missing.

Excluding the target value from the results should be simple enough with newer versions of excel, but with older versions there are other problems to overcome first.

You can change the references as you need, the formula only refers to column O (data column) and L2 (targe value).

 

[Pejayuk]

Thanks Jason, but I don't want to take up any more of your time with a possible new formula and maybe having to start from scratch. I had no idea it was going to be this complicated and your existing formula is already the most complex one I have ever seen. It would be very useful to be able to exclude the target value and then it will be ready to go when I am able to upgrade to a newer version.

 

[jasonb75]

This might work with your older version of excel. I ran a compatibility check and no issues were highlighted, but I think that it will exceed the number of nested functions allowed

With the sample list of values for testing in O3:O30 and criteria value in L2, enter this array formula into L3 and fill down as far as needed.

In the example below you will see that there are 5 results, this is because there are 3 values tied for third place (97,97,81). I was using randbetween to generate the list for testing and this situation caught my attention. The formula was filled down for about 20 rows with the rest returning blanks as would be expected.

Note that the results will always be in list order, sorting them within the formulas would be too complex to be practical (if even possible with the constraints of older excel versions).

Book1
	K	L			O
2	Criteria	89			List
3	Results	97			7
4		97			19
5		95			72
6		95			30
7		81			3
8					74
9					36
10					56
11					29
12					34
13					89
14					99
15					97
16					97
17					98
18					58
19					95
20					95
21					80
22					37
23					22
24					61
25					70
26					31
27					30
28					81
29					78
30					3
Sheet1

Cell Formulas
Range	Formula
L3:L8	L3	{=IF(ISERROR(SMALL(IF(IF($O$3:$O$30<>$L$2,ABS($O$3:$O$30-$L$2))<=SMALL(IF($O$3:$O$30<>$L$2,ABS($O$3:$O$30-$L$2)),3),ROW($O$3:$O$30)),ROWS(L$2:L2))),"",INDEX($O$1:$O$30,SMALL(IF(IF($O$3:$O$30<>$L$2,ABS($O$3:$O$30-$L$2))<=SMALL(IF($O$3:$O$30<>$L$2,ABS($O$3:$O$30-$L$2)),3),ROW($O$3:$O$30)),ROWS(L$2:L2))))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Pejayuk]

Thanks Jason, I look forward to trying it when I get home.