Results 1 to 14 of 14
Thread: array formula killing me (2003)

20070730, 18:49 #1
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,546
 Thanks
 39
 Thanked 69 Times in 65 Posts
array formula killing me (2003)
I have this ugly array formula that works in various cells EXCEPT in row 148 of this sheet. All of the cells are formatted the same way (accounting), but in this row, I'm getting FALSE in the cells rather than a $  . There isn't any hair left to pull out...so I'm hoping someone will spot my stupidity here. Thanks in advance. BTW, B148 is not blank and C148 is not zero and H8 is not blank.
=IF(OR(ISBLANK($B148),0=$C148),0,IF(ISBLANK($H$8), AVERAGE(IF(($B148=DATA!$AF$2:$AF$5001)*($C$4=DATA! $AC$2:$AC$5001)*((IF(ISBLANK($G$6),0,"FY05"=DATA!$ A$2:$A$5001))+(IF(ISBLANK($H$6),0,"FY06"=DATA!$A$2 :$A$5001))+(IF(ISBLANK($I$6),0,"FY07"=DATA!$A$2:$A $5001))+(IF(ISBLANK($J$6),0,"FY08"=DATA!$A$2:$A$50 01))+(IF(ISBLANK($K$6),0,"FY09"=DATA!$A$2:$A$5001) )),DATA!AR$2:AR$5001),IF(ISERROR(AVERAGE(IF(($B148 =DATA!$AF$2:$AF$5001)*($C$4=DATA!$AC$2:$AC$5001)*( $H$8<=DATA!$AO$2:$AO$5001)*($I$8>=DATA!$AO$2:$AO$5 001),DATA!AR$2:AR$5001))),0,AVERAGE(IF(($B148=DATA !$AF$2:$AF$5001)*($C$4=DATA!$AC$2:$AC$5001)*($H$8< =DATA!$AO$2:$AO$5001)*($I$8>=DATA!$AO$2:$AO$5001), DATA!AR$2:AR$5001))))))

20070730, 18:49 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: array formula killing me (2003)
This is a bit hard to interpret without seeing the workbook. Could you attach a stripped down copy of it?

20070730, 18:57 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,546
 Thanks
 39
 Thanked 69 Times in 65 Posts
Re: array formula killing me (2003)
I don't think so...there's so much in this workbook, I'm not sure how easily it would be to remove things. SIGH.
If the last AVERAGE is the one being calculated, when I select it and look at the result (F9), I don't get a 0, but do get a value.
I can't understand why the cell would read FALSE.

20070730, 19:23 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: array formula killing me (2003)
Hard to follow, but I believe that your very last IF statement doesn't have a comma after the value_if_true argument, that is, there is no value_if_false argument, and that can cause an unintended FALSE result. So see if changing (before all the closing parens):
,DATA!AR$2:AR$5001)
to
,DATA!AR$2:AR$5001,0)
helps, and check the entire formula to make sure the problem doesn't arise elsewhere.John ... I float in liquid gardens
UTC 7ąDS

20070730, 20:21 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,546
 Thanks
 39
 Thanked 69 Times in 65 Posts
Re: array formula killing me (2003)
I really appreciate the help. I have revised this to remove some of those IF conditions. I'm wondering if I have too many IFs in this line?! Since it's 2003, will I get funny results if I have more than 7 IF statements in one line? Or just not working?
IF(OR(ISBLANK($B148),0=$C148),0,IF(ISBLANK($H$8),A VERAGE((DATA!$AR$2:$AR$5001)*($B148=DATA!$AF$2:$AF $5001)*($C$4=DATA!$AC$2:$AC$5001)*((IF(ISBLANK($G$ 6),0,"FY05"=DATA!$A$2:$A$5001))+(IF(ISBLANK($H$6), 0,"FY06"=DATA!$A$2:$A$5001))+(IF(ISBLANK($I$6),0," FY07"=DATA!$A$2:$A$5001))+(IF(ISBLANK($J$6),0,"FY0 8"=DATA!$A$2:$A$5001))+(IF(ISBLANK($K$6),0,"FY09"= DATA!$A$2:$A$5001))),IF(ISERROR(AVERAGE((DATA!$AR$ 2ATA!$AR$5001)*($B148=DATA!$AF$2:$AF$5001)*($C$4=DA TA!$AC$2:$AC$5001)*($H$8<=DATA!$AO$2:$AO$5001)*($I $8>=DATA!$AO$2:$AO$5001))),0,AVERAGE((DATA!$AR$2:$ AR$5001)*($B148=DATA!$AF$2:$AF$5001)*($C$4=DATA!$A C$2:$AC$5001)*($H$8<=DATA!$AO$2:$AO$5001)*($I$8>=D ATA!$AO$2:$AO$5001))))))

20070730, 20:28 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: array formula killing me (2003)
The limitation is on nested functions: IF(IF(IF(...))), not on functions at one level: an expression such as IF(...)+IF(...)+IF(...)+... is limited by the maximum length of a formula (1,024 characters), not by the number of IFs.

20070730, 20:38 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: array formula killing me (2003)
"IF" formulas cannot be nested more than 7 levels deep; yours appears to be only 4 levels deep (your multiple "IF"'s joined by "+" are all at the same level). Did you try the single change I suggested?
(Can you change your post formatting so it is not one continuous line?)John ... I float in liquid gardens
UTC 7ąDS

20070730, 20:44 #8
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,546
 Thanks
 39
 Thanked 69 Times in 65 Posts
Re: array formula killing me (2003)
I removed many of the IF statements even tho it's not a nested issue.
The last AVERAGE should be what is calculated, and when I examine it from F9, it shows 25.098 but the cell shows FALSE.
The last AVERAGE computation is:
AVERAGE((DATA!$AR$2:$AR$5001)*($B148=DATA!$AF$2:$A F$5001)*($C$4=DATA!$AC$2:$AC$5001)*($H$8<=DATA!$AO $2:$AO$5001)*($I$8>=DATA!$AO$2:$AO$5001))
For the life of me, I can't figure out why FALSE shows up since I have either 0s or a value as the only options in the array formula.
This last AVERAGE section results from a FALSE test of an IF statement, so it should be the calculation.

20070730, 21:06 #9
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: array formula killing me (2003)
Is this <post:=663,743>post 663,743</post:> the present formula? You moved the target so I don't know which one you are referring to.
John ... I float in liquid gardens
UTC 7ąDS

20070730, 21:24 #10
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,546
 Thanks
 39
 Thanked 69 Times in 65 Posts
Re: array formula killing me (2003)
The complete array formula is:
IF(OR(ISBLANK($B148),0=$C148),0,IF(ISBLANK($H$8),A VERAGE((DATA!$AR$2:$AR$5001)*($B148=DATA!$AF$2:$AF $5001)*($C$4=DATA!$AC$2:$AC$5001)*((ISBLANK($G$6)* ("FY05"=DATA!$A$2:$A$5001))+(ISBLANK($H$6)*("FY06" =DATA!$A$2:$A$5001))+(ISBLANK($I$6)*("FY07"=DATA!$ A$2:$A$5001))+(ISBLANK($J$6)*("FY08"=DATA!$A$2:$A$ 5001))+(ISBLANK($K$6)*("FY09"=DATA!$A$2:$A$5001))) ,IF(ISERROR(AVERAGE((DATA!$AR$2ATA!$AR$5001)*($B148=DATA!$AF$2:$AF$5001)*($C$4=DA TA!$AC$2:$AC$5001)*($H$8<=DATA!$AO$2:$AO$5001)*($I $8>=DATA!$AO$2:$AO$5001))),0,AVERAGE((DATA!$AR$2:$ AR$5001)*($B148=DATA!$AF$2:$AF$5001)*($C$4=DATA!$A C$2:$AC$5001)*($H$8<=DATA!$AO$2:$AO$5001)*($I$8>=D ATA!$AO$2:$AO$5001))))))
B148 is not blank and C148 is not 0 and H8 is not blank...so, the logic should go to the last AVERAGE which shows (F9) as 25.098 but the cell is FALSE.
The format is accounting.
This is what it looks like when I use F9 through some of the tests...
IF(FALSE,0,IF(FALSE,AVERAGE((DATA!$AR$2:$AR$5001)* ($B148=DATA!$AF$2:$AF$5001)*($C$4=DATA!$AC$2:$AC$5 001)*((ISBLANK($G$6)*("FY05"=DATA!$A$2:$A$5001))+( ISBLANK($H$6)*("FY06"=DATA!$A$2:$A$5001))+(ISBLANK ($I$6)*("FY07"=DATA!$A$2:$A$5001))+(ISBLANK($J$6)* ("FY08"=DATA!$A$2:$A$5001))+(ISBLANK($K$6)*("FY09" =DATA!$A$2:$A$5001))),IF(FALSE,0,25.098))))

20070730, 21:35 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: array formula killing me (2003)
Does this do what you want?
=IF(OR(ISBLANK($B148),0=$C148),0,IF(ISBLANK($H$8), AVERAGE((Data!$AR$2:$AR$5001)*($B148=Data!$AF$2:$A F$5001)*($C$4=Data!$AC$2:$AC$5001)*((ISBLANK($G$6) *("FY05"=Data!$A$2:$A$5001))+(ISBLANK($H$6)*("FY06 "=Data!$A$2:$A$5001))+(ISBLANK($I$6)*("FY07"=Data! $A$2:$A$5001))+(ISBLANK($J$6)*("FY08"=Data!$A$2:$A $5001))+(ISBLANK($K$6)*("FY09"=Data!$A$2:$A$5001)) )),IF(ISERROR(AVERAGE((Data!$AR$2ata!$AR$5001)*($B148=Data!$AF$2:$AF$5001)*($C$4=Da ta!$AC$2:$AC$5001)*($H$8<=Data!$AO$2:$AO$5001)*($I $8>=Data!$AO$2:$AO$5001))),0,AVERAGE((Data!$AR$2:$ AR$5001)*($B148=Data!$AF$2:$AF$5001)*($C$4=Data!$A C$2:$AC$5001)*($H$8<=Data!$AO$2:$AO$5001)*($I$8>=D ata!$AO$2:$AO$5001)))))

20070730, 21:53 #12
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,546
 Thanks
 39
 Thanked 69 Times in 65 Posts
Re: array formula killing me (2003)
A misplaced set of parens or two, it seems...or, rather in the wrong place(s)...
Thanks for the additional set of eyes on this...with the correct placement of parens it seems to have done the trick.
Thank you very much!!

20070730, 22:15 #13
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: array formula killing me (2003)
Without knowing the underlying data so that it can be broken into segments, I can't audit this, but I'm almost certain you have a paren in the wrong place. When you empty the formula to the IFs, you can see it in the third line:
'IF(((),),,IF((),(()*()*()*((*())+(*())+(*())+(*() )+(*())),IF(((()*()*()*()*())),0,(()*()*()*()*())) )))
'IF((,),,IF(,(***((*)+(*)+(*)+(*)+(*)),IF((****),, (****))))))
'IF(,,IF(,<font color=red>(</font color=red>(),IF(,,)<font color=red>)</font color=red>)))
The red bolded open para closes at third last at the end of the entire expression; it should probably close before the next if. As is, you have a missing argument value_if_false argument for the second if. I can't tell where it should be for certain, but I'd guess that this is correct (with some other ISBLANK redundant paren eliminations):
IF(OR(ISBLANK($B148),0=$C148),0,IF(ISBLANK($H$8),A VERAGE((DATA!$AR$2:$AR$5001)*($B148=DATA!$AF$2:$AF $5001)*($C$4=DATA!$AC$2:$AC$5001)*((ISBLANK($G$6)* ("FY05"=DATA!$A$2:$A$5001))+ISBLANK($H$6)*("FY06"= DATA!$A$2:$A$5001)+ISBLANK($I$6)*("FY07"=DATA!$A$2 :$A$5001)+ISBLANK($J$6)*("FY08"=DATA!$A$2:$A$5001) +ISBLANK($K$6)*("FY09"=DATA!$A$2:$A$5001))),IF(ISE RROR(AVERAGE((DATA!$AR$2ATA!$AR$5001)*($B148=DATA!$AF$2:$AF$5001)*($C$4=DA TA!$AC$2:$AC$5001)*($H$8<=DATA!$AO$2:$AO$5001)*($I $8>=DATA!$AO$2:$AO$5001))),0,AVERAGE((DATA!$AR$2:$ AR$5001)*($B148=DATA!$AF$2:$AF$5001)*($C$4=DATA!$A C$2:$AC$5001)*($H$8<=DATA!$AO$2:$AO$5001)*($I$8>=D ATA!$AO$2:$AO$5001)))))))
Since you aren't comfortable posting the spreadsheet expample, i can't pursue this any further. I'd audit this by moving interior expressions, such as the two AVERAGE() and the ISERROR() expression into separate cells, in order to track the component build and paren matching more easily.
I used to build expressions this complicated, but it's unfair to expect any other present or future user to understand them, so I don't do that any more. Hint, hint. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>John ... I float in liquid gardens
UTC 7ąDS

20070730, 22:18 #14
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: array formula killing me (2003)
Gee, I was 40 minutes later, I could been having a beer! <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
John ... I float in liquid gardens
UTC 7ąDS