Using Excel N function with arrays

Question

A fairly basic question, but I have been unable to find a previous answer - perhaps partly because it is difficult to search on 'N function' without getting a lot of false matches.

The N function is a concise way of returning a number if a cell contains a number or zero if it contains text. It is shorter than using the ISNUMBER function and potentially useful in array formulae.

Why can I write

=SUM(N({1,2,3}))

and get the answer 6, but if I write

=SUM(N(A1:A3))

and A1:A3 contains some numbers, I just get the first number?

If I go to Evaluate Formula, it shows that it is not treating A1:A3 as an array, even if I enter it as an array formula.

Is there a way of coercing the N function to work in an array formula?

Answer 1

Try:

=SUM(N(+A1:A3))

The unary plus operator is here sufficient to coerce an array return from the range being passed.

It is also convenient that the results of applying the unary operator to each of the values in the range are such that, when we then pass them to N, the resulting values will be equivalent to those we would have obtained had we simply applied the N function to each.

Regards