Apparent "IF" nesting limit

agareau · General User Joined: 17 Mar 2010 Posts: 7

In converting some programs from my old spreadsheet to openoffice, I have come upon either an apparent limitation of OpenOffice or my lack of understanding of Calc. I cannot get Calc to calculate beyond 3 levels of Nesting. Any suggestions ?

agareau · General User Joined: 17 Mar 2010 Posts: 7

Upon further examination, the "IF" command can be nested much deeper than 3 levels when all of the cells are ccontiguous. However if they are separated, then it will "bomb" after 3 levels. As an example: the testing cell contains
=IF(A2<>" ";A2;IF(A3<>" ";A3;IF(A4<>" ";A4;IF(A5<>" ";A5;IF(A6<>" ";A6;IF(A7<>" ";A7;" "))))))
and the the cells being tested are contiguous and the "IF" command works to these 6 levels. However, if the cells being tested were positioned in 3 groups of 2, such as A2,A3,C2,C3,E2,E3 and using the following test command =IF(A2<>" ";A2;IF(A3<>" ";A3;IF(C2<>" ";C2;IF(C3<>" ";C3;IF(E2<>" ";E2;IF(E3<>" ";E3;" ")))))) then the command ceases to work beyond 3 levels.
Hope this strikes a chord with someone !

scsisys · OOo Enthusiast Joined: 17 Dec 2009 Posts: 165

The following works for me...

=IF(A2<>"";A2;IF(A3<>"";A3;IF(C2<>"";C2;IF(C3<>"";C3;IF(E2<>"";E2;IF(E3<>"";E3;IF(G2<>"";G2;IF(G3<>"";G3;IF(I2<>"";I2;IF(I3<>"";I3;""))))))))))

Copying your example and pasting into Calc (V 3.2.1) only worked for the A2 cell reference. Deleting the spaces between the " " to look like this "" through out the formula,
it then returned the correct values upon deleting a value in each of the named cells.

Ray

agareau · General User Joined: 17 Mar 2010 Posts: 7

Thanks Ray. I will play with this to see if the program functions properly. The cells being tested do get overwritten and sometimes "spaced" out. It's strange that it works for the first 3 levels and no more, yet when contiguous, there appears to be no problem. Unfortunately, I haven't learned how to report "Bugs" yet. Again, Thanks !
Andre