MIN of a difference?

M

Moe

I have a list of numbers (Ex. 95, 92, 105, 101, 110, 82). I need
formula to give me the closest number to 100 (in this case it would b
101). I was thinking of a MIN formula combined with an ABS function i
an array format? But I've tried a million different things and nothin
seems to work.

I appreciate anybody's ideas....

Mo
 
F

Frank Kabel

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A10,MATCH(MIN(ABS(A1:A20-lookup_value)),ABS(A1:A20-lookup_val
ue),0))
 
A

Alex Delamain

you were oh so close!!
If your data is in A1 - A20 then then following formula should be used

=MIN(ABS(100-A1:A20))

BUT you need to enter it as an Array formula - to do this you us
Ctrl+Shift+Enter instead of just enter. This shows up as curly bracket
round the formula when you view it and means it evaluates 100-A1, 100-A
et
 
Top