# 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. Answer: 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.