N(range) problem

When using the N() function on a range, the return is the value of the first cell instead of an array of values, even when used in an array computation:

=SUMPRODUCT(N(A1:A2))
returns SUMPRODUCT(N(A1))
instead of SUMPRODUCT({N(A1);N(A2)})

Is there any way to successfully compute the latter without volatile instructions or resorting to intermediary cells?

The reason for the N() function is that the range value may contain text, and would thus raise a #VALUE error.

For those interested, this is the full, yet faulty expression:
=SUMPRODUCT(--(ISNUMBER(C139:N139)),1/(N(C139:N139)+0.00000575))

A working, but volatile array formula:
{=SUM(IF(ISNUMBER(C139:N139),1/(C139:N139+0.00000575),0))}

Originally Posted by shg

Welcome to the forum.

None of those fomulas are volatile.

Thanks for the welcome

And obviously SUMPRODUCT isn't volatile, that's the whole idea.
However due to the N() function not working as i expected, the formula doesn't work.

The one that does work is the array function I mentioned at the very end, but all array functions are always volatile.

Originally Posted by shg

That is incorrect; only certain functions are volatile: OFFSET(), INDIRECT(), TODAY(), NOW(), CELL(), INFO(), RAND(), RANDBETWEEN()

Maybe they changed it in later versions, but i can assure you, in Excel2000 all array formulas (those entered with CTRL+SHIFT+ENTER) are volatile.

From your same site:
http://www.decisionmodels.com/optspeedj.htm

Remember that an array formula is a bit like a volatile formula: if any single one of the cells it references has changed or is volatile or has been recalculated then the array formula will evaluate ALL the cells it references.

Apparently he classifies it under a different name, but the effect is the same: it causes unneeded recalculations.

Anyways, this is taking us way of topic... If it makes you happy i'll reformulate my question:
Is there a non volatile, non array function way to compute this, without resorting to intermediary cells?
{=SUM(IF(ISNUMBER(C139:N139),1/(C139:N139+0.00000575),0))}