# Passing an array formula as an argument

## Question:

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:

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 the 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.