How to determine the minimum difference between values?
 

Does anyone have any suggestions on how to determine the minimum difference
between values?
There is a list of value under column A
307, 325, 343, 361, 381, 401, 421, 441
and there is a given value 400 in cell B1, I would like to determine the
minimum difference between 400 and the list of values, and it should return 1
in cell C1, because abs(400-401) = 1.
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

How to determine the minimum difference between values?
 

Type/paste this in C1 (after changing A10 to the last cell you want in Col A)
=MIN(ABS(A1:A10-$B$1))
and press CTRL-ALT-ENTER

"Eric" wrote:

Does anyone have any suggestions on how to determine the minimum difference
between values?
There is a list of value under column A
307, 325, 343, 361, 381, 401, 421, 441
and there is a given value 400 in cell B1, I would like to determine the
minimum difference between 400 and the list of values, and it should return 1
in cell C1, because abs(400-401) = 1.
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

How to determine the minimum difference between values?
 

and press CTRL-ALT-ENTER
the above is not working for me
CTRL+SHIFT+ENTER does work fine

"Sheeloo" wrote:

Type/paste this in C1 (after changing A10 to the last cell you want in Col A)
=MIN(ABS(A1:A10-$B$1))
and press CTRL-ALT-ENTER

"Eric" wrote:

Does anyone have any suggestions on how to determine the minimum difference
between values?
There is a list of value under column A
307, 325, 343, 361, 381, 401, 421, 441
and there is a given value 400 in cell B1, I would like to determine the
minimum difference between 400 and the list of values, and it should return 1
in cell C1, because abs(400-401) = 1.
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric

How to determine the minimum difference between values?
 

Thanks for pointing that out...

It was a typo... It should be CTRL-SHIFT-ENTER which is the method for
entering ARRAY formulas.

"claude jerry" wrote:

and press CTRL-ALT-ENTER
the above is not working for me
CTRL+SHIFT+ENTER does work fine

"Sheeloo" wrote:

Type/paste this in C1 (after changing A10 to the last cell you want in Col A)
=MIN(ABS(A1:A10-$B$1))
and press CTRL-ALT-ENTER

"Eric" wrote:

Does anyone have any suggestions on how to determine the minimum difference
between values?
There is a list of value under column A
307, 325, 343, 361, 381, 401, 421, 441
and there is a given value 400 in cell B1, I would like to determine the
minimum difference between 400 and the list of values, and it should return 1
in cell C1, because abs(400-401) = 1.
Does anyone have any suggestions on how to do it in Excel?
Thanks in advance for any suggestions
Eric