+ Reply to Thread
Results 1 to 4 of 4

IF Statement Limits

  1. #1
    Registered User
    Join Date
    06-05-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    46

    IF Statement Limits

    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.

  2. #2
    Registered User
    Join Date
    09-25-2015
    Location
    Wesdt Greenwich, RI
    MS-Off Ver
    2013
    Posts
    6

    Re: IF Statement Limits

    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.

  3. #3
    Registered User
    Join Date
    06-05-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2013
    Posts
    46

    Re: IF Statement Limits

    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.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF Statement Limits

    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")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA chart limits
    By Stevo56 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2015, 01:14 PM
  2. [SOLVED] IF AND OR complex statement for accepting limits in a tolerance range
    By maggiedort in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 08-22-2013, 12:22 PM
  3. Limits for using VLOOKUP
    By luissa01 in forum Excel General
    Replies: 4
    Last Post: 11-22-2011, 10:30 AM
  4. If nesting limits
    By timrmason in forum Excel General
    Replies: 3
    Last Post: 08-25-2011, 10:04 AM
  5. [SOLVED] Row Limits
    By jv in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2006, 12:50 PM
  6. Limits
    By Student in forum Excel General
    Replies: 1
    Last Post: 12-07-2005, 11:30 AM
  7. If Statement Limits
    By Lambright44 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-13-2005, 09:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1