Ah, yes. I see what you mean. I'm so used to N being used in conjunction with some other, appropriate function that I forget that, on its own, it struggles to coerce an array of returns.
But we need to be a bit more specific: it's not so much that N "won't work with arrays". It's more that it is not able to operate over an array which comprises a range of cells within the actual worksheet, without prior "deferencing" of that range.
For example, there is no problem whatsoever if we attempt:
=COUNT(N({"a",1,7}))
(which does not even require CSE).
If those elements are contained within actual cells, however, as in your example, we need to first "deference" them to their actual values. Often we see this when N is used in conjunction with e.g. OFFSET or INDIRECT, though there are much simpler methods. For example, with H4:J4 contained those values as above ("a", 1 and 7), a simple coercion to numerics, i.e.:
=COUNT(N(0+H4:J4))
with CSE, will resolve to:
=COUNT(N({#VALUE!,1,7}))
and then to:
=COUNT({#VALUE!,1,7})
i.e. 2.
However, this is not quite what you want here, since N("a") should be 0, not an error, as here.
One way to "dereference" ranges into their actual values, whilst retaining the datatype of those values, is:
=COUNT(N(IF({1},H4:J4)))
(No CSE required this time.)
And this nicely coerces (or "deferences") the values in the range H4:J4 into their cell contents, which can then be passed to N.
I take your point, though. It would be nice if N could have this property all on its own, without requiring such additional coercion.
And this is not the only function which behaves like this. DOLLARDE, DEC2BIN, IMSUM and CELL are just a few more examples. For example, with H4:J4 containing 1, 2 and 3:
=SUMPRODUCT(DOLLARDE(H4:J4,5))
will return #VALUE!, though:
=SUMPRODUCT(DOLLARDE(0+H4:J4,5))
correctly returns 6.
Regards
