Adding in a piece of logic to my complicated formula!

[Dan the Man]

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="Referred 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

 

[driller]

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 tools>formula
auditing>evaluate...
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="Referred 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(criteria5=true,"result5_if_true",IF(criteria6=true,"result6_if_true",IF(criteria7=true,"result7_if_true","RESULT IF NO CRITERIA WAS TRUE"))))))

regards,
driller

 

[Pete_UK]

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

 

[Dan the Man]

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="Referred 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="Referred to Tx"),"New Eval
Needed",""),"")))

So now that I almost have it, any final suggestions to tame this beast?

Thanks,

Dan