How can I pass an array formula as an argument to NumXL?

Q: I am trying to use the median absolute deviation (i.e. MAD) function in NumXL, but the input argument is generated by an IF Excel function on cells range, see example below:

= MAD(IF($C$3:$C$100=$M9, $D$3:$D$100))

Unfortunately when I do so, the function returns #NUM.


In short, yes you can.

Looking at your example, there are few modifications that we need to do:

  • Currently, the IF function returns corresponding value from column D if there is a match, and FALSE otherwise. FALSE is interpreted as zero, so we need to specify the #N/A value for no match
    = MAD(IF($C$3:$C$100=$M9, $D$3:$D$100, #N/A))
  • The Excel IF(.) function returns a single value, unless it is placed in an array form. To do so:
    • Select the cell where the formula is
    • Press F2 to edit the formula
    • Now, press CTRL+SHIFT+ENTER
    • The formula now is surrounded by italic braces (i.e. {.})
      {= MAD(IF($C$3:$C$100=$M9, $D$3:$D$100, #N/A))}

The array formula format is used on a single cell to force the IF(.) function to return the full array to NumXL MAD(.) Function.

Have more questions? Submit a request