The closest number

[avinashraste]

Dear all,
Hi, I have a set of numbers in the range of D1:G17. And a master number in cell A1. Now as I change the master number in cell A1, the numbers in the range also change.

1. Now is this possible to find the closest number which is higher than the master number ?
2. The second higher number ? till fifth higher number ?
3. The same way for lower side. The closest number which is lower than the master number.
4. The second lower number ? till fifth lower number.

please guide and help

Thanks & Regards

Avinash Raste

 

[barry houdini]

The smallest number higher than A1 can be found with this formula

=SMALL(IF(D1:G17>A1,D1:G17),1)

change the 1 at the end to a 2 for the next highest, then 3, 4 etc.

similarly for the largest number smaller than A1

=LARGE(IF(D1:G17< A1,D1:G17),1)

<?XML:NAMESPACE PREFIX = A1,D1 /><A1,D1:G17),1)< p> These are array formulas that need to be confirmed with CTRL+SHIFT+ENTER
</A1,D1:G17),1)<>

 

[avinashraste]

Dear Barry,

Thank you very much for this early reply. When I tried to paste it, it says #VALUE! error...what should I do ? Any idea ?

Thanks & Regards
Avinash Raste

 

[barry houdini]

Try this process

Paste the formula in a cell and select that cell

Press F2 key to select formula

Hold down CTRL and SHIFT keys and press ENTER

You should now see curly braces like { and } around the formula and the formula should work

 

[Ron Coderre]

If remembering to use CTRL+SHIFT+ENTER is an issue...
try this variation:

=SMALL(INDEX(D1:G17+(D1:G17<=A1)*10^10,0),1)

Does that help?

 

[avinashraste]

Hi,

Thanks a lot for replying. There is a little problem. For upper side if its working fine then it creates problem for the lower side as even after changing the last number from 1 to 2 , it gives the same previous number. If I change 1 to 3 and 3 to 5, it gives fine. But then if I change the master number again starts giving problem...

Avinash Raste

 

[barry houdini]

Do you have duplicate numbers?

 

[avinashraste]

Dear Barry,

You are absolutely right. I do have some duplicate numbers..but it can not be avoided. Is there any way out ??

Avinash Raste

 

[barry houdini]

OK, let's say you use this formula in M2

=SMALL(IF(D1:G17>A1,D1:G17),1)

confirmed with CTRL+SHIFT+ENTER

Then in M3 use this version

=SMALL(IF(D$1:G$17>M2,D$1:G$17),1)

confirm with CTRL+SHIFT+ENTER and copied down to M6

Now M2:M6 should give the 5 numbers immediately greater than A1, without duplicates

You can do the same with the other formula

 

[Ron Coderre]

Elegant.