challenge! ISNA() and complex array formula

[Lorin]

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(Unrounded_2007)-SUM(ROUND(Unrounded_2007,0))),0)))),0)}
In case it matters the named ranges are:
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.

 

[Ron Rosenfeld]

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(Unrounded_2007)-SUM(ROUND(Unrounded_2007,0))),0)))),0)}
In case it matters the named ranges are:
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

 

[Lorin]

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

 

[Ron Rosenfeld]

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

Glad to help. Thanks for the feedback.

--ron