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

## 0 Comments