Home > Enterprise >  Error in ABS When Non-Numeric Value is in Array
Error in ABS When Non-Numeric Value is in Array

Time:02-18

I have a formula =IFERROR(INDEX($A$1:$A$24, MATCH(MIN(ABS($B$1:$B$24)), ABS($B$1:$B$24),0)),"NA") and it does not work when there is a cell in array containing non-numeric value. For instance, I have this data:

Column A Column B
6.990 -3.105
6.875 -2.875
6.750 -2.625
6.625 -2.375
6.500 -2.125
6.375 -1.875
6.250 -1.625
6.125 -1.375
5.990 -1.105
5.875 -0.875
5.750 -0.625
5.625 -0.375
5.500 -0.125
5.375 0.125
5.250 0.500
5.125 0.750
4.990 1.020
4.875 1.250
4.750 1.625
4.625 2.000
4.500 3.125
4.375 3.625
4.250 4.125
4.125 5.000
4.000 NA

Because I have NA in the last cell, The result of the formula will be #VALUE! How can I fix the formula so I does not lead to an error?

CodePudding user response:

Wrap the ABS part in IFERROR:

=INDEX($A$1:$A$25, MATCH(MIN(IFERROR(ABS($B$1:$B$25),99999)), ABS($B$1:$B$25),0))

Please note that some versions of Excel will require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode to force the array entry of the formula.

enter image description here

  • Related