+ Reply to Thread
Results 1 to 4 of 4

More than 7 IF's Nested?

  1. #1
    Registered User
    Join Date
    07-27-2007
    Posts
    27

    More than 7 IF's Nested?

    Hi All,

    I have a formula with 7 IFs Nested within an IF formula. I also have some AND functions nested within it. I need to add more IFs, is this possible? Here is a copy of the current working formula:

    =IF(AND(GST="",K28<>""),"Please enter",IF(AND(GST<>"",K28<>"",F28=""),"<----- Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28=""),"<----- Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28<>"",E28="Enter Date Above"),"<----- Enter ",IF(K28="SP",220,IF(K28="TPR",109.09,IF(K28="TD",136.36,IF(K28="TA",100,""))))))))

    I just want to add another IF at the end like this:

    =IF(AND(GST="",K28<>""),"Please enter",IF(AND(GST<>"",K28<>"",F28=""),"<----- Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28=""),"<----- Enter ",IF(AND(GST<>"",K28<>"",F28<>"",C28<>"",E28="Enter Date Above"),"<----- Enter ",IF(K28="SP",220,IF(K28="TPR",109.09,IF(K28="TD",136.36,IF(K28="TA",100,IF(K28="TA",100,"")))))))))

    But this comes up with an error, I assume that there must be a maximum number of items to nest?

    Is there away around this?

    This formula is what I have created inside an invoice that I get my staff to use. The idea is that it is Idiot proof, ie if they forget to enter whether or not they are registered for GST, then it will tell them. If one of the items on the form are missing, it will tell them, but then the last couple are for their pay.

    Make any sense? think I have just confused myself... lol

    Any help would be appreciated.

    Clint

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2007, 2013
    Posts
    6,523
    Hi DeeLaa,

    Try this formula:
    copy to clipboard
    Please Login or Register  to view this content.
    It only uses 5 IF's and a LOOKUP (Excel has a limit of 7 nested IF's, as you've found out). It's a little longer due to the ISERROR check in the LOOKUP section so it can return "" if the LOOKUP does not find a result.

  3. #3
    Registered User
    Join Date
    07-27-2007
    Posts
    27
    Cheers will give it a go.

    Clint

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Deelaa7

    Please read forum rules and message to cross posters and attached the link to the other forum or forums where you posted this question

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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