closet to 0
Thanks JE.
"JE McGimpsey" wrote:
Briefly:
x: IF(B1:B13<"", ABS(A1:A13)
returns an array of the absolute values for each cell in A1:A13 for
which there's a corresponding value in B1:B13. If there's no
corresponding B value, the array entry contains FALSE.
y: MIN(x)
returns the minimum value in the array of absolute values.
z: MATCH(y, ABS(A1:A13), FALSE)
returns the index of the minimum value within the range of absolute
values
and INDEX(B1:B13, z)
returns the value in B1:B13 corresponding to that index.
In article ,
dhstein wrote:
I was reading the post and had no idea how to do that.
I tried your solution and it works. But I have no idea what it's doing or
why it works. Any chance you could explain it a little bit? Thanks.
"JE McGimpsey" wrote:
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
=INDEX(B1:B13, MATCH(MIN(IF(B1:B13<"", ABS(A1:A13))), ABS(A1:A13),
FALSE))
|