|
Adding in a piece of logic to my complicated formula!
Previously I received wonderful help with a very long piece of formula, and
that which you see below works like a charm! =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AQ7="","Check AC Status",IF(AND(TODAY()EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through",""),""))) I have to add in one last line to accomplish the final requirement, and hopefully someone can help me. When I attempt to incorporate the information it doesn't work, however if I put the information into its own stand alone cell it works just fine. Here is the additional piece of formula I'd like to weave into the above "beast": =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed","") Not sure if this is enough information for you all, or if you more details about the "requirement" surrounding the logic. Thanks in advance! Dan |
Adding in a piece of logic to my complicated formula!
Dan The Man,
from the beasty formula you have... from the first *if* =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed Before New RF",............ ***i try to evaluate the result by using the toolsformula auditingevaluate... i found out that the evaluate tool box bear a result of ***#NAME?****.. Yet the cell result shows as what u may expect without error result.... from the new addl *if*which u say can stand alone in one cell...i agree as per my test.. =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed",...... THEN when i try to append the new addl. if as the first part of the formula, like this... =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed",IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7< "",AN7="Referred to Tx"),"Updated Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AQ7="","Check AC Status",IF(AND(TODAY()EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through",""),"")))) and then when i try to *enter* , a pop-up appear saying *formula error* and the *today()* inside the 3rd from the last *if* had been highlighted... there may be a twist in your beast of formula, but i suggest to formulate your multi-ifs with something like this...with priority result in the first line of *if*.. =IF(criteria1=true,"result1_if_true",IF(criteria2= true,"result2_if_true",IF(criteria3=true,"result3_ if_true",IF(criteria4=true,"result4_if_true",IF(cr iteria5=true,"result5_if_true",IF(criteria6=true," result6_if_true",IF(criteria7=true,"result7_if_tru e","RESULT IF NO CRITERIA WAS TRUE")))))) regards, driller -- ***** birds of the same feather flock together.. "Dan the Man" wrote: Previously I received wonderful help with a very long piece of formula, and that which you see below works like a charm! =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AQ7="","Check AC Status",IF(AND(TODAY()EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through",""),""))) I have to add in one last line to accomplish the final requirement, and hopefully someone can help me. When I attempt to incorporate the information it doesn't work, however if I put the information into its own stand alone cell it works just fine. Here is the additional piece of formula I'd like to weave into the above "beast": =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed","") Not sure if this is enough information for you all, or if you more details about the "requirement" surrounding the logic. Thanks in advance! Dan |
Adding in a piece of logic to my complicated formula!
Following on from Driller's suggestion, I would suggest that you
arrange your IFs like so: =IF(criteria1=true,"result1_if_true","") & IF(criteria2=true,"result2_if_true","") & IF(criteria3=true,"result3_if_true","") & IF(criteria4=true,"result4_if_true","") & IF(criteria5=true,"result5_if_true","") & IF(criteria6=true,"result6_if_true","") & IF(criteria7=true,"result7_if_true","") The criteria should be in priority order, and mutually exclusive. This way you can string along many more IFs than the limit of 7 nested IFs as none of them are nested, and if you end up with a blank cell then there has not been any criteria which has been met. Hope this helps. Pete On Jul 4, 12:28 am, driller wrote: Dan The Man, from the beasty formula you have... from the first *if* =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed Before New RF",............ ***i try to evaluate the result by using the toolsformula auditingevaluate... i found out that the evaluate tool box bear a result of ***#NAME?****.. Yet the cell result shows as what u may expect without error result.... from the new addl *if*which u say can stand alone in one cell...i agree as per my test.. =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed",...... THEN when i try to append the new addl. if as the first part of the formula, like this... =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed",IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7< "",AN7="Referred to Tx"),"Updated Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AQ7="","Check AC Status",IF(AND(TODAY()EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through",""),"")))) and then when i try to *enter* , a pop-up appear saying *formula error* and the *today()* inside the 3rd from the last *if* had been highlighted... there may be a twist in your beast of formula, but i suggest to formulate your multi-ifs with something like this...with priority result in the first line of *if*.. =IF(criteria1=true,"result1_if_true",IF(criteria2= true,"result2_if_true",IF*(criteria3=true,"result3 _if_true",IF(criteria4=true,"result4_if_true",IF(c r*iteria5=true,"result5_if_true",IF(criteria6=true ,"result6_if_true",IF(crite*ria7=true,"result7_if_ true","RESULT IF NO CRITERIA WAS TRUE")))))) regards, driller -- ***** birds of the same feather flock together.. "Dan the Man" wrote: Previously I received wonderful help with a very long piece of formula, and that which you see below works like a charm! =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AQ7="","Check AC Status",IF(AND(TODAY()EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through",""),""))) I have to add in one last line to accomplish the final requirement, and hopefully someone can help me. When I attempt to incorporate the information it doesn't work, however if I put the information into its own stand alone cell it works just fine. Here is the additional piece of formula I'd like to weave into the above "beast": =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed","") Not sure if this is enough information for you all, or if you more details about the "requirement" surrounding the logic. Thanks in advance! Dan- Hide quoted text - - Show quoted text - |
Adding in a piece of logic to my complicated formula!
Thanks guyz!
The portion of my "beasty" formula that you see below works well. =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AP7="","Check AC Status",IF(AND(TODAY()EDATE(AP7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF", The string below doesn't seem to register during testing, however it does not create any error messages when incorporated. It is as if I never included this string. IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")), This last string does work, and flags according to the logic. IF(AL7<"",IF(AND(TODAY()EDATE(AL7,12),AN7="Refer red to Tx"),"New Eval Needed",""),""))) So now that I almost have it, any final suggestions to tame this beast? Thanks, Dan "Pete_UK" wrote: Following on from Driller's suggestion, I would suggest that you arrange your IFs like so: =IF(criteria1=true,"result1_if_true","") & IF(criteria2=true,"result2_if_true","") & IF(criteria3=true,"result3_if_true","") & IF(criteria4=true,"result4_if_true","") & IF(criteria5=true,"result5_if_true","") & IF(criteria6=true,"result6_if_true","") & IF(criteria7=true,"result7_if_true","") The criteria should be in priority order, and mutually exclusive. This way you can string along many more IFs than the limit of 7 nested IFs as none of them are nested, and if you end up with a blank cell then there has not been any criteria which has been met. Hope this helps. Pete On Jul 4, 12:28 am, driller wrote: Dan The Man, from the beasty formula you have... from the first *if* =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed Before New RF",............ ***i try to evaluate the result by using the toolsformula auditingevaluate... i found out that the evaluate tool box bear a result of ***#NAME?****.. Yet the cell result shows as what u may expect without error result.... from the new addl *if*which u say can stand alone in one cell...i agree as per my test.. =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed",...... THEN when i try to append the new addl. if as the first part of the formula, like this... =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed",IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7< "",AN7="Referred to Tx"),"Updated Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AQ7="","Check AC Status",IF(AND(TODAY()EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through",""),"")))) and then when i try to *enter* , a pop-up appear saying *formula error* and the *today()* inside the 3rd from the last *if* had been highlighted... there may be a twist in your beast of formula, but i suggest to formulate your multi-ifs with something like this...with priority result in the first line of *if*.. =IF(criteria1=true,"result1_if_true",IF(criteria2= true,"result2_if_true",IF-(criteria3=true,"result3_if_true",IF(criteria4=tru e,"result4_if_true",IF(cr-iteria5=true,"result5_if_true",IF(criteria6=true," result6_if_true",IF(crite-ria7=true,"result7_if_true","RESULT IF NO CRITERIA WAS TRUE")))))) regards, driller -- ***** birds of the same feather flock together.. "Dan the Man" wrote: Previously I received wonderful help with a very long piece of formula, and that which you see below works like a charm! =IF(AND(TODAY()EDATE(AL7,12),AA7<"",AL7<"",AN7= "Referred to Tx"),"Updated Eval Needed Before New RF",IF(OR(AA7<"",AND(TODAY()AG7,AN7="AC Initiated")),IF(AND(TODAY()AG7,AN7="AC Initiated"),IF(AQ7="","Check AC Status",IF(AND(TODAY()EDATE(AQ7,6),AN7="AC Initiated"),"Re-Check AC Status","")),IF(AND(TODAY()EDATE(AA7,12),AN7="Ref erred to TX"),IF(AL7="","Eval Before RF",IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")),IF(AL7<"",IF(AND(TODAY()EDATE (AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through",""),""))) I have to add in one last line to accomplish the final requirement, and hopefully someone can help me. When I attempt to incorporate the information it doesn't work, however if I put the information into its own stand alone cell it works just fine. Here is the additional piece of formula I'd like to weave into the above "beast": =IF(AND(TODAY()EDATE(AL7,12),AA7="",AL7<"",AN7=" Referred to Tx"),"New Eval Needed","") Not sure if this is enough information for you all, or if you more details about the "requirement" surrounding the logic. Thanks in advance! Dan- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 12:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com