challenge! ISNA() and complex array formula
 

Here's the problem:
=ISNA(S2) works fine (the result is TRUE)
But when I replace S2 with the formula (pasted in) from cell S2 and
press Ctrl+Shift-Enter, it breaks. Excel tells me "The formula you
typed contains an error." and won't let me proceed.

The formula is S2 is this (it is a single-cell array formula, of course
I don't type in the curly braces):
{MATCH(M2-N2,SMALL(Unrounded_2007-Rounded_2007,ROW(INDIRECT("1:"&ROUND(ABS(SUM(Unrou nded_2007)-SUM(ROUND(Unrounded_2007,0))),0)))),0)}
In case it matters the named ranges a
Unrounded_2007 = sheet!$M$2:$M$40
Rounded_2007 = sheet!$N$2:$N$40

Any advice much appreciated. Otherwise I am going to have to give up on
using arrays and write some VBA for this problem.

challenge! ISNA() and complex array formula
 

On 2 Jan 2006 16:27:24 -0800, "Lorin" wrote:

Here's the problem:
=ISNA(S2) works fine (the result is TRUE)
But when I replace S2 with the formula (pasted in) from cell S2 and
press Ctrl+Shift-Enter, it breaks. Excel tells me "The formula you
typed contains an error." and won't let me proceed.

The formula is S2 is this (it is a single-cell array formula, of course
I don't type in the curly braces):
{MATCH(M2-N2,SMALL(Unrounded_2007-Rounded_2007,ROW(INDIRECT("1:"&ROUND(ABS(SUM(Unrou nded_2007)-SUM(ROUND(Unrounded_2007,0))),0)))),0)}
In case it matters the named ranges a
Unrounded_2007 = sheet!$M$2:$M$40
Rounded_2007 = sheet!$N$2:$N$40

Any advice much appreciated. Otherwise I am going to have to give up on
using arrays and write some VBA for this problem.

The problem is that your formula above is at the maximum function nesting level
for Excel which is seven (7). When you try to embed your formula in the ISNA
function, you now are nesting to eight levels which is illegal.

One solution would be to use intermediate cells to contain some of the factors
in your function. For example, you could remove the:

ROW(INDIRECT("1:"&ROUND(ABS(SUM(Unrounded_2007)-SUM(ROUND(Unrounded_2007,0))),0)))

and place that in some intermediate cell (or use it as a NAME'd formula, and
then refer to that cell in your function.


--ron

challenge! ISNA() and complex array formula
 

Thank you SO much! That's the problem I was missing!

challenge! ISNA() and complex array formula
 

On 2 Jan 2006 19:52:51 -0800, "Lorin" wrote:

Thank you SO much! That's the problem I was missing!

Glad to help. Thanks for the feedback.

--ron