writting a Close formula

[Rwatson]

I have a list of numbers and i want a formula that can look at all of them and tell me which number is closest to say, "one" and return its value (maybe something like .98)


so it would look like this.


2.45

1.68

1.3

.987

.95


the formula would return .987 as the closest to one.


Instead of min, max or average, i want a "close" formula if thats at all possible.


thanks

 

[Luke M]

Confirm this formula using Ctrl+Shift+enter

=INDEX(A2:A6,MATCH(MIN(ABS(A2:A6-1)),ABS(A2:A6-1),0))

 

[Sajan]

Hello Rwatson,


I have assumed that you want the closest value that is smaller than the number being sought, and return zero if the value being sought is smaller than the smallest value.


Assuming your number range is named "list", and the value you are searching is in C1, then you could try the following:

=MAX((IF(COUNTIF(C1,">="&list),list)))

entered with Ctrl + Shift + Enter


Cheers,

Sajan.