If Max or Ron can answer this they are Gods!

[Dan the Man]

Hey Guyz!

Since Max and Ron have been so helpful, I'll throw out a more complex
formula. My best friend is a senior software engineer, however since he's got
married, he hasn't had a lot of time for my spreadsheet problems. He created
the spreadsheet for me, but I've been left to my own devices since he's taken
his wife.

Here is another formula I am working with (which works):

=IF(OR(AA7<>"",OR(AND(TODAY()>AG7,(AN7="AC
Initiated")))),IF(AND(TODAY()>AG7,(AN7="AC Initiated")),IF(AP7="","Check AC
Status",IF(AND(TODAY()>(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC
Status","")),IF(AND(TODAY()>EDATE(AA7,12),OR(AN7="Referred to
TX")),IF(AL7="","Eval Before
RF",IF(AND(TODAY()>(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm AC
Follow
Through","")),"")),IF(AL7<>"",IF(AND(TODAY()>EDATE(AL7,6),OR(AN7="Referred to
Tx")),"Confirm AC Follow Through",""),""))

The following are additional requirements I want to encorporate into the
above "beast of a formula":

A flag entitled "Update Eval Before Hearing" should appear if more than a
year elapses (from the date that the evaluation was posted in Row AL). To
clarify, the "Confirm AC Follow Through" flag appears if more than 6 months ,
but less than 12 months has elapsed (which the above formula does without
difficulty), and the new "Update Eval Before Hearing", should appear if more
than 1 year elapses. The addition to the formula also presumes that a Hearing
date was posted in Row AA.

I tried to integrate the following piece of logic into the above formula (to
address the above requirement), but it didn't work:

IF(AND(TODAY()>(EDATE(AL7,12)),OR(AN7="Referred to Tx")),IF(AA7<>"","Update
Eval Before Hearing","")),""))

I realize that this is a very long and detailed formula, so I understand if
it's asking too much............

Happy 4th!

Dan

 

[Rick Rothstein \(MVP - VB\)]

Without studying your formula in any detail, and based solely on your
description of the addition you want and the fact that the existing formula
works correctly, I would guess that putting this (don't miss the comma at
the end)...

IF(TODAY()>EDATE(AL7,12),"Update Eval Before Hearing",

in front of the formula you now have (that is, after the equal sign but
before the first IF) and placing a closing parenthesis at the end of your
existing formula, would do what you want.

Rick

 

[Dan the Man]

Thanks Rick. That got me closer, but still not 100%. With your suggestion,
the "Update Eval Before Hearing" flag does appear if the date in Cell AL7 is
greater than 12 months, however if I enter a date under 6 months, the flag
"Confirm AC follow Through" still shows up. That flag should only appear (per
the formula below) if more than 6 months, but less than 12 months has
elapsed. I appreciate your suggestion.

Dan

 

[Rick Rothstein \(MVP - VB\)]

Sorry, I apparently misread your 2nd "large" paragraph as indicating your
existing formula worked as expected accept for the greater than one year
problem. We should be able to handle the less than six months problem with
another IF function (I think<g>). Instead of what I suggested earlier, try
putting this...

IF(TODAY()>EDATE(AL7,12),"Update Eval Before
Hearing",IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow Through",

in front of the **ORIGINAL** formula you had (that is, after the equal sign
but before the first IF) and put TWO closing parentheses at the end of that
original formula. Now, there is a chance that you will have some
non-essential testing conditions left over inside that original formula (I'm
not 100% sure) as a result of this, but it shouldn't matter... the only
dates it will have to process are those where AL is less than 6 months (any
other condition testing for a larger AL date, if in there, will not
activate).

Rick

 

[Dan the Man]

I think I read your instructions correctly Rick, but Excel didn't seem to
like the additions you gave me (Excel can be so testy at times, lol). I
separated out your suggestion so you can see it better. I also added the two
additional parenthesis at the end of the entire formula..........Dan

=IF(TODAY()>EDATE(AL7,12),"Update Eval Before
Hearing",IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow Through")),

IF(OR(AA7<>"",OR(AND(TODAY()>AG7,(AN7="AC
Initiated")))),IF(AND(TODAY()>AG7,(AN7="AC Initiated")),IF(AP7="","Check AC
Status",IF(AND(TODAY()>(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC
Status","")),IF(AND(TODAY()>EDATE(AA7,12),OR(AN7="Referred to
TX")),IF(AL7="","Eval Before
RF",IF(AND(TODAY()>(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm AC
Follow
Through","")),"")),IF(AL7<>"",IF(AND(TODAY()>EDATE(AL7,6),OR(AN7="Referred to
Tx")),"Confirm AC Follow Through",""),""))))

 

[Rick Rothstein \(MVP - VB\)]

See the single inline comment....

I think I read your instructions correctly Rick, but Excel didn't seem to
like the additions you gave me (Excel can be so testy at times, lol). I
separated out your suggestion so you can see it better. I also added the
two
additional parenthesis at the end of the entire formula..........Dan

=IF(TODAY()>EDATE(AL7,12),"Update Eval Before
Hearing",IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow Through")),

You added two parentheses to the end of the above that I did not ask for
(the two you added at the end of the original formula below are correct,
those I asked for).

=IF(TODAY()>EDATE(AL7,12),"Update Eval Before
Hearing",IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow Through",

Remove them and see if that makes a difference.

Rick

 

[Greg Wilson]

Dan,

The formula contains five OR functions that contain only one argument and
thus are superfluous. There are also a few instances of unnecessary
parentheses which only add to the complexity. The nested IF structure also
appears to be poorly constructed because condtions get tested more than once
at different nesting levels.

Unnecessary OR function and unnecessary parentheses example:

OR(AND(TODAY()>AG7,(AN7="AC Initiated")))

Example of condtion(s) being tested more than once at different nesting
levels. Note how [ Today()>AG7 ] and [ AN7="AC Initiated" ] is tested twice:

=IF(OR(AA7<>"",OR(AND(TODAY()>AG7,(AN7="AC
Initiated")))),IF(AND(TODAY()>AG7,(AN7="AC Initiated"))...

The OR function is allowed to have only one argument and will return True if
the arguement is true, but this serves no purpose. The argument by itself
achieves the same.

The following is a simplification that removes unnecessary OR functions and
parentheses. It is NOT an answer to your question:

=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",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",""),""))

If Rick is not successful at solving your problem, then I suggest you
describe a theoretical (simplified) worksheet layout that models the actual
but is as simple as you can make it. Also provide example data and describe
what you want to achieve. Alternatively, make the workbook available.

Greg

 

[Dan the Man]

I altered the formula per your suggestion below Rick, but alas no luck. I
appreciate your time very much. I'll keep at it. I will also look at Greg's
suggested with respect to the "nested" IF statements and cleaning the current
formula up a bit. Dan

=IF(TODAY()>EDATE(AL7,12),"Update Eval Before
Hearing",IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow
Through",IF(TODAY()>EDATE(AL7,12),"Update Eval Before
Hearing",IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow
Through",IF(OR(AA7<>"",OR(AND(TODAY()>AG7,(AN7="AC
Initiated")))),IF(AND(TODAY()>AG7,(AN7="AC Initiated")),IF(AP7="","Check AC
Status",IF(AND(TODAY()>(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC
Status","")),IF(AND(TODAY()>EDATE(AA7,12),OR(AN7="Referred to
TX")),IF(AL7="","Eval Before
RF",IF(AND(TODAY()>(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm AC
Follow
Through","")),"")),IF(AL7<>"",IF(AND(TODAY()>EDATE(AL7,6),OR(AN7="Referred to
Tx")),"Confirm AC Follow Through",""),""))))

 

[Pete_UK]

I presume you are using Excel 2007, as you have 11 IFs in the formula
- Excel 2003 and earlier will only support up to 7 nested IFs.

Hope this helps.

Pete

 

[Roger Govier]

Hi

Recasting your formula in the following way makes it a little easier to
see what is going on
=
IF(TODAY()>EDATE(AL7,12),"Update Eval Before Hearing",
IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow Through",
IF(TODAY()>EDATE(AL7,12),"Update Eval Before Hearing",
IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow Through",
IF(OR(AA7<>"",OR(AND(TODAY()>AG7,(AN7="AC Initiated")))),
IF(AND(TODAY()>AG7,(AN7="AC Initiated")),IF(AP7="","Check AC
Status",
IF(AND(TODAY()>(EDATE(AP7,6)),OR(AN7="AC Initiated")),"Re-Check AC
Status","")),
IF(AND(TODAY()>EDATE(AA7,12),OR(AN7="Referred to TX")),
IF(AL7="","Eval Before RF",
IF(AND(TODAY()>(EDATE(AL7,6)),OR(AN7="Referred to Tx")),"Confirm
AC Follow Through","")),"")),
IF(AL7<>"",
IF(AND(TODAY()>EDATE(AL7,6),OR(AN7="Referred to Tx")),"Confirm AC
Follow Through",""),""))))


There are 12 IF's, which will not work unless your are using XL2007.
Versions before this only support 7 levels of nesting.
As you can se, the first 2 lines are duplicated in lines 3 and 4

There are a lot of superfluous OR's and parentheses in your formula, and
I believe it can be simplified to the following.

=
IF(OR(AA7<>"",AND(TODAY()>AG7,AN7="AC Initiated"),"",
IF(TODAY()>EDATE(AL7,12),"Update Eval Before Hearing",
IF(TODAY()>EDATE(AL7,6),"Confirm AC Follow Through",
IF(AND(TODAY()>AG7,AN7="AC Initiated",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",AL7=""),"Eval Before
RF",
IF(AND(AL7<>"",TODAY()>EDATE(AL7,6),AN7="Referred to Tx"),"Confirm AC
Follow Through",""))))))

It is difficult to follow and I am not at all certain that this is
correct.
However, setting it out in this format, may help you to see things more
clearly and thus be able to ensure that each of the relevant conditions
is met.

There are ways of having more than 7 IF's by using named formulae
together. Take a look at
http://www.cpearson.com/excel/nested.htm

 

[Greg Wilson]

Roger, your simplified formula has 25 left parentheses and 23 right
parentheses. If we just add the missing parentheses to the end and collapse
the two child functions at the 3rd nesting level of the resulting formula ,
the structure can be seen as follows:

= IF(OR(AA7<>"",AND(),"", IF()))

As you can see, the resulting main parent IF function has only one argument,
an OR function. This OR function has four arguments, one of which is empty
parentheses, and therefore is erroneous. The formula has nine nesting levels
when not collapsed and thus would require xl2007.

We concur that the original formula has superfluous OR functions and
unnecessary parentheses that only add to the complexity. Also, the IF
structure is apparently very inefficient since conditions are repeat-tested
at different nesting levels. I think the formula could be greatly simplified
but requires a thorough understanding of the worksheet layout and of what is
needed. After simplification, xl2007 may not be required. My conclusion
remains the same as what I originally posted.

Regards,
Greg

 

[driller]

Dan the Man,

for the beast of formula u had and the additional If formula u need,,,the
status remark you need are as follows...
--------------
1) Update Eval Before Hearing,
2) Confirm AC Follow Through,
3) Re-Check AC Status,
4) Check AC Status,
5) Eval Before RF,
6) "" BLANK
--------------
From the above #status, please narrate your conditions AND/OR...i have
filled some conditions for your fill-in....**<>=?**
1) RESULT ="Update Eval Before Hearing" --> [TODAY()>EDATE(AL7,12),
AA7<>="", AG7<>="", AL7<>"", AN7="?", AP7<>=""]
2) RESULT ="Confirm AC Follow Through" --> [TODAY()>EDATE(AL7,6), AA7=<>"",
AG7=<>"", AL7<>"", AN7="Referred to TX", AP7=<>""]
3) RESULT ="Re-Check AC Status" --> [TODAY()>EDATE(AP7,6), AA7<>="",
AG7<>="", AL7<>="", AN7="AC Initiated", AP7<>""]
4) RESULT ="Check AC Status" --> [TODAY()>AG7, AA7<>="", AG7<>"", AL7<>="",
AN7="AC Initiated", AP7=""]
5) RESULT ="Eval Before RF" --> [TODAY()>EDATE(AA7,12), AA7<>"", AG7<>="",
AL7="", AN7="Referred to TX", AP7<>=""]
6) RESULT="" *BLANK*--> [AA7<>="", AG7<>="", AL7<>="", AN7="?", AP7<>=""]
Are the dates on AA7,AG7,AL7,AP7 linked together thru formulas or encoded
dates.?

Is there any status remark when the result needed is "completed" specially
when today()<all other dates on AA7,AG7,AL7,AP7....?

regards,
driller

 

[Roger Govier]

Hi Greg

I agree.
I was not suggesting that my posting was a solution, (it was written in
Notepad not Excel hence the inequality of parentheses wasn't picked up).
What I was trying to do was to get the formula set out more clearly for
the OP to try to understand what was going on, by putting each If
statement on a separate line and trying to trim out most of the
duplication.

Again, I agree, that without a better knowledge of exactly what they are
trying to achieve, it is difficult to post a solution.

 

[Dan the Man]

Today is my birthday, and Driller and others helped me piece together
everything I needed to solve my problem. The added piece that you gave me
Driller (line 1 Result) was the final solve. The formula now works just as I
wanted it to.

I have a few more additions I need to make to this beastly formula, but for
now I am content with what you helped me to solve.

I'm an Excel "newbie" who has relied primarily on trial and error to learn
how to utilize the power of this program. I have Excel 2002 btw, and my
formula as it stands now works BEAUTIFULLY. Isn't it funny how you can keep
trying with frustration and lack of success, and all of a sudden it works.
Maybe this was my birthday gift, lol?

I'll come back to this group when I finish writing the requirements for the
final pieces of this formula. As it stands, here is what now works:

=IF(AND(TODAY()>EDATE(AL7,12),AA7<>"",AL7<>"",AN7="Referred to Tx"),"Updated
Eval Needed",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",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",""),"")))

Dan the Man,

for the beast of formula u had and the additional If formula u need,,,the
status remark you need are as follows...
--------------
1) Update Eval Before Hearing,
2) Confirm AC Follow Through,
3) Re-Check AC Status,
4) Check AC Status,
5) Eval Before RF,
6) "" BLANK
--------------
From the above #status, please narrate your conditions AND/OR...i have
filled some conditions for your fill-in....**<>=?**
1) RESULT ="Update Eval Before Hearing" --> [TODAY()>EDATE(AL7,12),
AA7<>="", AG7<>="", AL7<>"", AN7="?", AP7<>=""]
2) RESULT ="Confirm AC Follow Through" --> [TODAY()>EDATE(AL7,6), AA7=<>"",
AG7=<>"", AL7<>"", AN7="Referred to TX", AP7=<>""]
3) RESULT ="Re-Check AC Status" --> [TODAY()>EDATE(AP7,6), AA7<>="",
AG7<>="", AL7<>="", AN7="AC Initiated", AP7<>""]
4) RESULT ="Check AC Status" --> [TODAY()>AG7, AA7<>="", AG7<>"", AL7<>="",
AN7="AC Initiated", AP7=""]
5) RESULT ="Eval Before RF" --> [TODAY()>EDATE(AA7,12), AA7<>"", AG7<>="",
AL7="", AN7="Referred to TX", AP7<>=""]
6) RESULT="" *BLANK*--> [AA7<>="", AG7<>="", AL7<>="", AN7="?", AP7<>=""]
Are the dates on AA7,AG7,AL7,AP7 linked together thru formulas or encoded
dates.?

Is there any status remark when the result needed is "completed" specially
when today()<all other dates on AA7,AG7,AL7,AP7....?

regards,
driller
--
*****
birds of the same feather flock together..

Roger, your simplified formula has 25 left parentheses and 23 right
parentheses. If we just add the missing parentheses to the end and collapse
the two child functions at the 3rd nesting level of the resulting formula ,
the structure can be seen as follows:

= IF(OR(AA7<>"",AND(),"", IF()))

As you can see, the resulting main parent IF function has only one argument,
an OR function. This OR function has four arguments, one of which is empty
parentheses, and therefore is erroneous. The formula has nine nesting levels
when not collapsed and thus would require xl2007.

We concur that the original formula has superfluous OR functions and
unnecessary parentheses that only add to the complexity. Also, the IF
structure is apparently very inefficient since conditions are repeat-tested
at different nesting levels. I think the formula could be greatly simplified
but requires a thorough understanding of the worksheet layout and of what is
needed. After simplification, xl2007 may not be required. My conclusion
remains the same as what I originally posted.

Regards,
Greg