Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to Tame the "formula" beast, PLEASE?
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 (which attaches to my "beasty" formula above) has been separated out from the above formula for the purpose of clarity in this posting. It seems to work appropriately (creating the Confirm AC Follow Through Flag) ONLY when a date is input into cell AA7. However, the string of formula below should ALSO "register" and produce the applicable flag (Confirm AC Follow Through), when a date input into cell AL7 (and NO date is placed in cell AA7). IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")), This last string of the formula (which comes after that which you see above) 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? I suspect that I need to add in one more piece of logic in order for the formula to work 100%. Again, it is only the string I pointed out (under the single condition mentioned) that doesn't seem to want to play :( Thanks very much for any help offered! HAPPY 4th of July all! Dan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to Tame the "formula" beast, PLEASE?
I think part of the problem is that you can only have 7 levels of embedded if
statements. one method to get around this is to use choose() if you have 29 or fewer options =choose((AND(TODAY()EDATE(AL7,12))+2*AND(TODAY() AG7,AN7="AC Initiated")+3*...,"Updated Eval Needed Before New RF","Check AC Status", ...) You will have to take care that your options are not duplicated and are appropriate for the choose sselection such that onely one will be correct at any given time. "Dan the Man" wrote: 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 (which attaches to my "beasty" formula above) has been separated out from the above formula for the purpose of clarity in this posting. It seems to work appropriately (creating the Confirm AC Follow Through Flag) ONLY when a date is input into cell AA7. However, the string of formula below should ALSO "register" and produce the applicable flag (Confirm AC Follow Through), when a date input into cell AL7 (and NO date is placed in cell AA7). IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")), This last string of the formula (which comes after that which you see above) 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? I suspect that I need to add in one more piece of logic in order for the formula to work 100%. Again, it is only the string I pointed out (under the single condition mentioned) that doesn't seem to want to play :( Thanks very much for any help offered! HAPPY 4th of July all! Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to Tame the "formula" beast, PLEASE?
Thank you for the suggestion BJ. I will try that.
Dan "bj" wrote: I think part of the problem is that you can only have 7 levels of embedded if statements. one method to get around this is to use choose() if you have 29 or fewer options =choose((AND(TODAY()EDATE(AL7,12))+2*AND(TODAY() AG7,AN7="AC Initiated")+3*...,"Updated Eval Needed Before New RF","Check AC Status", ...) You will have to take care that your options are not duplicated and are appropriate for the choose sselection such that onely one will be correct at any given time. "Dan the Man" wrote: 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 (which attaches to my "beasty" formula above) has been separated out from the above formula for the purpose of clarity in this posting. It seems to work appropriately (creating the Confirm AC Follow Through Flag) ONLY when a date is input into cell AA7. However, the string of formula below should ALSO "register" and produce the applicable flag (Confirm AC Follow Through), when a date input into cell AL7 (and NO date is placed in cell AA7). IF(AND(TODAY()EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC Follow Through","")),"")), This last string of the formula (which comes after that which you see above) 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? I suspect that I need to add in one more piece of logic in order for the formula to work 100%. Again, it is only the string I pointed out (under the single condition mentioned) that doesn't seem to want to play :( Thanks very much for any help offered! HAPPY 4th of July all! Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
conditional formula to show "open" or "closed" | Excel Worksheet Functions | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |