CODEWITHSUNDEEP
excelexcel-formula
Tom Sharpe
Tom Sharpe

Reputation: 34230

Using Excel N function with arrays

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?

Upvotes: 4

Views: 2293

Answers (2)

XOR LX
XOR LX

Reputation: 7742

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

Upvotes: 7

Trum
Trum

Reputation: 630

Another option appears to be - {=N(SUM(A1:A3))} (entered as an array)

Upvotes: 0

Related Questions