How to remove #N/A from cell if I am using an array formula and can not use IFERROR function|General Excel Questions & Answers|Excel Forum|My Online Training Hub

The error is not actually returned by the formula, which is why IFERROR doesn't work, it's because the result array has fewer 'rows' than the range you have entered the formula into, so Excel reflects that by showing you #N/A. You could use this normally entered formula entered into H2 and copied down:

=IF(COUNT(MODE.MULT($B$2:$D$21))<ROWS($A$1:$A1),"",INDEX(MODE.MULT($B$2:$D$21),ROWS($A$1:$A1)))

COUNT(MODE.MULT($B$2:$D$21)) tells you how many numbers are returned by your original function (7 in this example)

ROWS($A$1:$A1) returns a sequence from 1 as you copy it down (since it becomes ROWS($A$1:$A2), then ROWS($A$1:$A3 and so on)

so for each row, the first part of the formula checks if the number of values in the result array is less than the number of rows we have copied the formula into, and returns "" if we have run out of numbers.

Now, since MODE.MULT($B$2:$D$21) returns an array of numbers, we can pass this to an INDEX function to return one specific value. In the first formula row, we are using:

INDEX(MODE.MULT($B$2:$D$21),ROWS($A$1:$A1))

which is the same as:

INDEX(MODE.MULT($B$2:$D$21),1)

so it returns the first value in the array. In the next row, ROWS($A$1:$A2) returns 2, so we get the second value, and so on.

Does that help?

Sorry, it was implicit in this sentence:

"the first part of the formula checks if the number of values in the result array is less than the number of rows we have copied the formula into"