| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
Threaded Mode
