MIN of a difference?

  • Thread starter Thread starter Moe
  • Start date Start date
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
 
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))
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Formula Question 5
MIN of a difference? 1
help with max, min, <, > in array functions 1
Lookup through a Range of Values in different cells 10
Help with some array formulas 1
number layout 5
Finding a value and returning corresponding info 3
Complicated vlookup/min-max query...please help 6

Back
Top