Formula for closest number

G

gaz_chops

Well-known Member

Joined

Apr 29, 2003

Messages

6,485

Platform

1. MacOS

• Apr 6, 2015

• #2

Hi Gerry, if you enter 11 should it not return 11, as that is present in the list and is therefore the closest match?

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Apr 6, 2015

• #3

Hi Gaz!
right I don't need the same number of the criteria
es. in this case if i digit 11 it should give 9 becauose 9 is the closest number to 11

 

Upvote 0

G

gaz_chops

Well-known Member

Joined

Apr 29, 2003

Messages

6,485

Platform

1. MacOS

• Apr 6, 2015

• #4

If your numbers are sorted in ascending order, try
=VLOOKUP(D1-1,A1:A8,1)

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Apr 6, 2015

• #5

hello Gaz
Your formula is not correct because the formula founds only the closest number when is smaller of the criteria but i need also bigger of the criteria
es. criteria 18 it should return 19
thank you

 

Upvote 0

G

gaz_chops

Well-known Member

Joined

Apr 29, 2003

Messages

6,485

Platform

1. MacOS

• Apr 6, 2015

• #6

I can find nearest above or below, the problem is ignoring the value that is an actual match!

 

Upvote 0

I

Ingolf

Banned

Joined

Mar 20, 2011

Messages

809

• Apr 6, 2015

• #7

What if your number in D1 is 5 and number in column will be

1
2
4
5
6

Then what is the number you need 4 or 6?

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Apr 6, 2015

• #8

@INGOLF
in this case both 4 and 6

 

Upvote 0

G

gaz_chops

Well-known Member

Joined

Apr 29, 2003

Messages

6,485

Platform

1. MacOS

• Apr 6, 2015

• #9

This finds nearest +/- but if an actual match it will return that!
Array - CSE

=INDEX(A1:A16,MATCH(MIN(ABS(A1:A16-D1)),ABS(A1:A16-D1),0))

 

Upvote 0

GerryZ

Well-known Member

Joined

Jul 4, 2014

Messages

1,224

Office Version

1. 365

Platform

1. Windows

• Apr 6, 2015

• #10

gaz_chops said:

I can find nearest above or below, the problem is ignoring the value that is an actual match!

Click to expand...

for the actual match we can ignore the with an IF statment! mybe

 

Upvote 0