Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
challenge! ISNA() and complex array formula
Thank you SO much! That's the problem I was missing!
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|