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.
Answer:
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.
Comments
Please sign in to leave a comment.