Formula for closest number

[GerryZ]

Hello everybody
I need a formula in D2 that find th closest number (criteria number in D1)
esemple if in D1 is 9 should give me back 11 but if in D2 i digit 5 it should give me 4
thank yu for your help

Book1
	A	B	C	D
1	1		CRITERIA	6
2	4		RESULT	5
3	5
4	9
5	11
6	15
7	18
8	19
Foglio1

 

[Aladin Akyurek]

@Aladin great
in case I need both number?
example criteria D1 I've 4 and in the list I've 1-3-4-5-9-21
i want both 3 and 5 because they both have the same distance from 4
hope is clear

See (a) and (b) of post #19...

 

[GerryZ]

See (a) and (b) of post #19...

hello Aldin i tried both of your formulas they solve the problem when I ve two numbers close es. 3-4-5 but now I ve another Problem
because I need not only < or > than criteria but both numbers < and > than CRITERIA
that's why i will use NARAYANK991 that's is more specific for my case


=INDEX(List,MATCH(MIN(ABS(IF(List<>CRITERION,List) - CRITERION)),ABS(IF(List<>CRITERION,List) - CRITERION),0))

Thank you Aldin anyway for your help

 

[Aladin Akyurek]

hello Aldin i tried both of your formulas they solve the problem when I ve two numbers close es. 3-4-5 but now I ve another Problem
because I need not only < or > than criteria but both numbers < and > than CRITERIA
...

No idea what you are talking about. The suggestions I made deliver you two numbers one closest from left (or from above) and one closest from right (or from below).

 

[GerryZ]

No idea what you are talking about. The suggestions I made delivers you two numbers one from left (or from above) and one from right (or from below).

Yes is clear, your formulaS work great but I need only ONE formula that try to guess which is the closest frome above and below

 

[Aladin Akyurek]

Yes is clear, your formulaS work great but I need only ONE formula that try to guess which is the closest frome above and below

Ok; What do you expect to see as result if:

Row\Col	A​	B​	C​	D​
1​	1​	​	CRITERIA	-3​
2​	2​	​	RESULT	​
3​	-3​	​	​	​
4​	4​	​	​	​
5​	5​	​	​	​
6​	6​	​	​	​
7​	7​	​	​	​
8​	8​

 

[GerryZ]

I don't have negative numbers
any way in this case will be number 1

 

[XOR LX]

Hi Gerry.

1) Is your list always in strictly ascending order?
2) Are there ever any duplicates in the list?
3) Is there an upper bound on the size of the values in the list, e.g. 9, 99, 999?

Regards

 

[GerryZ]

Hi Gerry.

1) Is your list always in strictly ascending order?
2) Are there ever any duplicates in the list?
3) Is there an upper bound on the size of the values in the list, e.g. 9, 99, 999?

Regards

1) ascending ordere YES
2) not dulicates
3) YES can be any numer from 1 to 10000000000
thank you!

 

[Aladin Akyurek]

I don't have negative numbers
any way in this case will be number 1

D2, control+shift+enter, not just enter:

=INDEX(A1:A8,MATCH(MIN(ABS(IF(A1:A8=D1,9.99999999999999E+307,A1:A8-D1))),ABS(A1:A8-D1),0))

 

[GerryZ]

I found another way
that return above and belove closest number that is exactly what i needed
thank you everybody for the help the Thread is closed

Book1
	A	B	C	D
1	2		CRITERIO	19
2	3		RISULTATO	21 - 18
3	4
4	6
5	11
6	15
7	18
8	19
9	21
10	35
11	36
12	54
13	62
14	75
15	78
16	82
Foglio1

Cell Formulas
Range	Formula
D2	{=INDEX(list,MATCH(MIN(ABS(IF(list>D1,list)-D1)),ABS(IF(list>D1,list)-D1),0))&" - "&INDEX(list,MATCH(MIN(ABS(IF(list)-D1)),ABS(IF(list)-D1),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
list	=Foglio1!$A$1:$A$16