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]

Are you including empty cells in the list range?

I haven't looked at it in detail, but I suspect that this could be the problem.

@DanteAmor
Simply filling the formula to 3 cells will not work as the OP needs in all cases, the requirement was for 3 closest values, which could be more than 3 results in the event of duplicates / ties (see my earlier post with xl2bb table).

 

[DanteAmor]

The OP's comment in post #4 is that he wants to see the duplicates in the results, but I don't understand where he says that if there is a duplicate the result count should be raised to 4 results, if there are 2 duplicates the count should be raised to 5 results, what happens if all the numbers are equal, in that case all the results should be shown, and I don't see these considerations as explained by the OP.

I hope the OP clarifies it.

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

 

[jasonb75]

Raising the count to 4 results in the event of qualifying duplicates / ties was also covered in post #4.

Raising it to 5 (or more) results when there are more duplicates / ties was not explicitly requested, but if you look at the test results in post #18 you will see that the OP was working with 6 results.

Also, given that it was mentioned in post #1 that there would be a 'minimum number of duplicates' it seems feasible that 5 or 6 should be a safe maximum.

 

[Pejayuk]

Hi Jason, there are no blank cells in my test list and there won't be any in the actual data.

I must admit, I am now getting very confused myself! But to try to clarify what I said in post #4. Firstly, I never want the target value shown in the results, as it will have been picked from the list in the first place and is already known. However, if there are duplicates of the target value, I would like these displayed. In this example list: 10,20,30,30,40,50,60,70,80,90. With a target of 20 it would return 10,30,40. Target of 30 would return 30,20,40.

In some rare cases if there were to be tied results, it would be desirable for the number of results to be expanded to show these. Jason's example in post #9 demonstrates this perfectly.

@DanteAmor. Thanks for your formula. Yes I have tried it with lots of different test ranges and it works most of the time. I notice though it doesn't always return all the exact results I need. I will try it again tomorrow and post an example or two, once I've worked out exactly which ones are wrong.

 

[DanteAmor]

@DanteAmor. Thanks for your formula. Yes I have tried it with lots of different test ranges and it works most of the time. I notice though it doesn't always return all the exact results I need. I will try it again tomorrow and post an example or two, once I've worked out exactly which ones are wrong.

As I said in post #22 we need to know the complete requirement, including all exceptions, otherwise we will only be guessing.

 

[jasonb75]

I tried the same test method as you did in post 18, the only way that I could get 0 results was to include blank / empty cells in the list range.

Looking at the latest bit of clarification, where the criteria should be excluded but duplicates of the criteria inclued, I think that you're going to be out of luck with a formula for excel 2003.
A UDF (vba function) should be up to the task, but as Dante said above, we need to be sure that all requirements are clearly defined before proceding.

 

[Pejayuk]

Hi Jason, I tried your formula again after you said it tested okay and you were right, it was because I hadn't filled the list range all the way down to row 30. Previously, I was just making sure there weren't any blank cells between data.

I'm very pleased with how it is working, but I can see the slight limitation you mentioned regarding duplicates of the criteria. I don't think the VBA route would be for me, as I'd find it far too complicated and it doesn't sound like it would be adaptable.

I think as a compromise, would it be possible to adapt this formula to include all the duplicates. (As I think was the case in the first one you did with the Aggregate function). I could probably then use both versions side-by-side.

Many thanks

 

[jasonb75]

This sort of does it, but it does omit the last result in some instances.

=IF(ISERROR(SMALL(IF(ABS($O$3:$O$30-$L$2)<=SMALL(ABS($O$3:$O$30-$L$2),3),ROW($O$3:$O$30)),ROWS(L$2:L2))),"",INDEX($O$1:$O$30,SMALL(IF(ABS($O$3:$O$30-$L$2)<=SMALL(ABS($O$3:$O$30-$L$2),3),ROW($O$3:$O$30)),ROWS(L$2:L2))))

The fail point that I observed in a quick test using randbetween was when the criteria appeared in the list exactly 3 times.

Based on your earlier posts it is my understanding that in this case, that you would want the criteria shown twice (the repeats, but not the original) plus the next closest value (including any duplicates or ties).

What the formula is actually doing is showing the criteria 3 times, with no other results because it thinks that the next result is actually the fourth closest value.

I don't think that it will be possible to improve on the current suggestions without the use of vba, even in more up to date versions of excel.

 

[jasonb75]

This appears to fix the problem that I mentioned above, but I have only a very quick test so there may be other issues that I haven't seen yet.

=IF(ISERROR(SMALL(IF(ABS($O$3:$O$30-$L$2)<=SMALL(ABS($O$3:$O$30-$L$2),3+(COUNTIF($O$3:$O$30,$L$2)=3)),ROW($O$3:$O$30)),ROWS(L$2:L2))),"",INDEX($O$1:$O$30,SMALL(IF(ABS($O$3:$O$30-$L$2)<=SMALL(ABS($O$3:$O$30-$L$2),3+(COUNTIF($O$3:$O$30,$L$2)=3)),ROW($O$3:$O$30)),ROWS(L$2:L2))))

Array confirmed as before.

 

[Pejayuk]

Hi Jason, Thanks for having another go at the formula. The criteria is now displayed fine. The only thing is, I'm only getting the 2 nearest instead of 3 (as the third is now the criteria). In other words, the previous formula didn't include the criteria and so returning 3 results was fine. Now it would need to return a minimum of 4 results.

Testing it with values of 10-280 in increments of 10 and a criteria of 80, it returns 70,80,90 instead of 70,80,60,90,100.

When there are 3 instances of the criteria, it shows all 3 and the next nearest which is perfect.

Apologies if I have not been as clear as I should have been in previous posts.