+ Reply to Thread
Results 1 to 4 of 4

FALSE returning from long nested IF formula but can't work out why

  1. #1
    Registered User
    Join Date
    03-22-2017
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    8

    FALSE returning from long nested IF formula but can't work out why

    Hi, I have this nested if formula that works with the exception that it returns "FALSE" in the cell if non of the conditions are met. It will return all the correct responses when error conditions are encountered. I can't see why it doesn't return a blank cell as per the "" at the end of the formula. I suspect it's something really obvious but I think I have now been staring at this formula too long. Any help would be really appreciated - thank you.


    =IF(X20="Duplicate","DUPLICATE",IF(AND(J20<>K20,L20<>I20/G20,I20<>J20*G20,J20>=10,I20<>J20+O20,D20<>"Session",T20=S20,Q20="PRIVATE",O20=0),"PRIVATE BILL ERROR",IF(AND(J20<>K20,L20<>I20/G20,I20<>J20*G20,J20>=10,I20<>J20+O20,D20<>"Session",T20=S20,Q20="INTERNAL"),"INTERNAL BILL ERROR",IF(AND(J20<>K20,L20<>I20/G20,I20<>J20*G20,J20>=10,I20<>J20+O20,D20<>"Session",T20=S20,Q20<>"PRIVATE",Q20<>"INTERNAL"),"BILLING ERROR",IF(AND(Q20="PRIVATE",O20<>0),IF(AND(Q20="MyWay",O20<>0),IF(AND(J20<>K20,L20<>I20/G20,I20<>J20*G20,J20>=10,I20<>J20+O20,D20<>"Session",T20=S20,Q20="PRIVATE"),"BILL & TRAVEL ERROR PRIVATE",IF(AND(Q20="PRIVATE",O20<>0),"PRIVATE TRAVEL ERROR",""))))))))

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: FALSE returning from long nested IF formula but can't work out why

    try: =IF(X20="Duplicate","DUPLICATE",IF(AND(J20<>K20,L20<>I20/G20,I20<>J20*G20,J20>=10,I20<>J20+O20,D20<>"Session",T20=S20,Q20="PRIVATE",O20=0),"PRIVATE BILL ERROR",IF(AND(J20<>K20,L20<>I20/G20,I20<>J20*G20,J20>=10,I20<>J20+O20,D20<>"Session",T20=S20,Q20="INTERNAL"),"INTERNAL BILL ERROR",IF(AND(J20<>K20,L20<>I20/G20,I20<>J20*G20,J20>=10,I20<>J20+O20,D20<>"Session",T20=S20,Q20<>"PRIVATE",Q20<>"INTERNAL"),"BILLING ERROR",IF(AND(Q20="PRIVATE",O20<>0),IF(AND(Q20="MyWay",O20<>0),IF(AND(J20<>K20,L20<>I20/G20,I20<>J20*G20,J20>=10,I20<>J20+O20,D20<>"Session",T20=S20,Q20="PRIVATE"),"BILL & TRAVEL ERROR PRIVATE",IF(AND(Q20="PRIVATE",O20<>0),"PRIVATE TRAVEL ERROR",""))),"")))))
    you forgot to define value_if_false for bolded IF
    Last edited by sandy666; 03-22-2017 at 09:26 AM.

  3. #3
    Registered User
    Join Date
    03-22-2017
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    8

    Re: FALSE returning from long nested IF formula but can't work out why

    That's fantastic - thank you so much, works perfectly.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: FALSE returning from long nested IF formula but can't work out why

    If the basic problem is solved
    - it's always a good practice to show respect to the person(s) who have helped you
    - is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
    - mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,

    Thanks

+ 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. Nested IF Statement returning False
    By pranjal79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-25-2016, 12:23 PM
  2. Nested If function returning FALSE instead of number
    By erimhast in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2015, 07:47 PM
  3. Nested IF Returning False - Need to Return True for Three Scenarios
    By rhelmer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2015, 05:50 PM
  4. [SOLVED] Nested IF statements are returning FALSE values - cannot find error in code!
    By apex_chio in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-03-2013, 11:01 PM
  5. Nested IF returning FALSE value help
    By crash884 in forum Excel General
    Replies: 3
    Last Post: 08-13-2013, 01:31 PM
  6. Replies: 5
    Last Post: 06-15-2010, 10:51 AM
  7. formula using if and sum is returning false value.
    By hpcoolt in forum Excel General
    Replies: 4
    Last Post: 10-02-2009, 06:19 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