Hi! I am trying to put 24 IF(AND( statement in a formula. But it seems like Excel does not allow that many statements in one formula. Is there a way around it. Here is what it looks like:
Thanks
Please Login or Register to view this content.
Hi! I am trying to put 24 IF(AND( statement in a formula. But it seems like Excel does not allow that many statements in one formula. Is there a way around it. Here is what it looks like:
Thanks
Please Login or Register to view this content.
Hello H.noor,
It comes down to which version of Excel you're using. I'm not sure on which versions allow what but I do remember that older versions of Excel (2003 and older) there was only 7 nested IF statements allowed. The later versions I believe allow more than 7.
I am running Office 2016. It allows up to 64 nests. It should be working as I only have 24. So I am not sure why it won't work.
It's not just the number of if's that can be nested (I believe it's 64 in XL2007+)
But also the overal character length of the formula.
However, perhaps if you reverse the logic..
Instead of testing for all the different scenarios that result in "Lapse"
Test only for the 1 scenario that results in ""
Logic says if that scenairo is NOT TRUE, it therefor must be one of the other scenarios, therefor return "Lapse"
=IF(AND(F3>G3,F3<H3,F3>I3,F3>J3,G3<H3,G3<I3,G3<J3,H3>I3,H3>J3,I3>J3),"","Lapse")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks