Nested If statemens

JackieKim

I've read various posts on the forum and have been able to fix a few of my issues but I can't seem to get past this one.

To show the stage of a project is in a workflow, I am using various IF(AND(NOT(ISBLANK, meaning that stage has been completed. So, if the Outreach stage is complete, it will not be blank, etc. In my AND formula I need to evaluate whether a client has hardware and if they do, then if the hardware has been delivered. This statement was working.

=IF(AND(NOT(ISBLANK([Scope Doc Complete]@row)), NOT(ISBLANK([CSS Outreach]@row)), NOT(ISBLANK([CSS Trans Meeting]@row)), NOT(ISBLANK([Time Implementer Assigned]@row)), NOT(ISBLANK([Configuration Complete]@row)), NOT(ISBLANK([Client Trained]@row))), IF(AND(NOT(ISBLANK([TS Hardware]@row)), NOT(ISBLANK([Hardware Received]@row))), "Awaiting Migration", "Test"))

However, the last statement is to evaluate if the migration stage is complete. To determine this, the pay date must be earlier than today and

=IF(AND(NOT(ISBLANK([Scope Doc Complete]@row)), NOT(ISBLANK([CSS Outreach]@row)), NOT(ISBLANK([CSS Trans Meeting]@row)), NOT(ISBLANK([Time Implementer Assigned]@row)), NOT(ISBLANK([Configuration Complete]@row)), NOT(ISBLANK([Client Trained]@row))), IF(AND(NOT(ISBLANK([TS Hardware]@row)), NOT(ISBLANK([Hardware Received]@row)))), NOT(ISBLANK([Migration Complete]@row)), "Extract", "Test")

Please help.

I really thought I could just start with evaluating just the 'migration complete' field but it appears the most complex statement has to be evaluated first. Is there a book or guide for formulas?

Paul Newcome

It looks like you have a couple of issues going on. The first one I see is that you don't have an output for the first IF statement. You also have some misplaced parenthesis in the second.

=IF(AND(NOT(ISBLANK([Scope Doc Complete]@row)), NOT(ISBLANK([CSS Outreach]@row)), NOT(ISBLANK([CSS Trans Meeting]@row)), NOT(ISBLANK([Time Implementer Assigned]@row)), NOT(ISBLANK([Configuration Complete]@row)), NOT(ISBLANK([Client Trained]@row))), "output if first if/and is true", IF(AND(NOT(ISBLANK([TS Hardware]@row)), NOT(ISBLANK([Hardware Received]@row)), NOT(ISBLANK([Migration Complete]@row))), "Extract", "Test"))

Sameer Karkhanis

As Paul said you have multiple things going on :-), so lets get step by step. Firstly, I have just tried to make your IF statement syntactically correct as below,

=IF(AND(NOT(ISBLANK([Scope Doc Complete]@row)), 
            NOT(ISBLANK([CSS Outreach]@row)), 
            NOT(ISBLANK([CSS Trans Meeting]@row)), 
            NOT(ISBLANK([Time Implementer Assigned]@row)), 
            NOT(ISBLANK([Configuration Complete]@row)), 
            NOT(ISBLANK([Client Trained]@row))), 
            IF(AND(NOT(ISBLANK([TS Hardware]@row)), NOT(ISBLANK([Hardware Received]@row))), 
                IF(NOT(ISBLANK([Migration Complete]@row)), "Extract", "Test"))  
        )

Unfortunately, I do not know what your columns types are in the sheet but I can say that for most part you would generally not need all those ISBLANK validations in the formula. Secondly, what is the last condition you want to add and what is the default value you are expecting if the condition is not met? Once you make that clear it is either just a tweak to the last IF (may be add a criteria or another IF). Let us know and we can help.

JackieKim

Thank you, Paul. The 2nd IF statement is actually part of the first AND. So... if those fields are blank and if the client has hardware that has been delivered, then the transition phase is "Extract". I didn't think I needed all of the ISBLANK either but I think for the results to be in chronological order I needed to include these.

Paul Newcome

You could just nest them all in the same AND function.

=IF(AND(NOT(ISBLANK([Scope Doc Complete]@row)), NOT(ISBLANK([CSS Outreach]@row)), NOT(ISBLANK([CSS Trans Meeting]@row)), NOT(ISBLANK([Time Implementer Assigned]@row)), NOT(ISBLANK([Configuration Complete]@row)), NOT(ISBLANK([Client Trained]@row)), NOT(ISBLANK([TS Hardware]@row)), NOT(ISBLANK([Hardware Received]@row)), NOT(ISBLANK([Migration Complete]@row))), "Extract", "Test")

JackieKim

Thank you. Is there a way to have an IF as part of the AND? I can't nest them together because not all clients have hardware. If all other stages are complete and the hardware has been delivered (only if they have hardware) then...

Paul Newcome

Which single column do you use to specify whether they have hardware or not?

JackieKim

TS Hardware

Paul Newcome

Ok. So just to make sure I have it all understood...

This part is needed for ALL rows:

IF(AND(NOT(ISBLANK([Scope Doc Complete]@row)), NOT(ISBLANK([CSS Outreach]@row)), NOT(ISBLANK([CSS Trans Meeting]@row)), NOT(ISBLANK([Time Implementer Assigned]@row)), NOT(ISBLANK([Configuration Complete]@row)), NOT(ISBLANK([Client Trained]@row))),

And this part is only needed if [TS Hardware]@row is not blank:

IF(AND(NOT(ISBLANK([TS Hardware]@row)), NOT(ISBLANK([Hardware Received]@row)), NOT(ISBLANK([Migration Complete]@row)))

If that is correct then this should work for you:

=IF(AND([Scope Doc Complete]@row <> "", [CSS Outreach]@row <> "", [CSS Trans Meeting]@row <> "", [Time Implementer Assigned]@row <> "", [Configuration Complete]@row <> "", [Client Trained]@row <> "", IF([TS Hardware]@row <> "", AND([Hardware Received]@row <> "", [Migration Complete]@row <> ""), 1 = 1)), "Extract", "Test")

JackieKim

Thank you so so much! Can you point me in the direction of learning more about the 1=1?

Paul Newcome

I'm not sure there is any documentation on it, so I'll try to explain. Basically we want to use the IF to output an AND to be evaluated if TS Hardware is not blank. If it is blank, then we want to output a "true" value for the larger AND statement. 1=1 is "true".

So if TS Hardware is not blank, include this "AND" statement. Otherwise skip over it by including a "true".

To be honest, I have never actually used this approach before, so I am not sure if I am explaining it very well. Please do let me know if that makes sense.