I need help on a multiple IF statement formula

Gavin Worsley

I'm struggling to think through the logic to allow for the required scenarios.

1. If the row is a master row - leave no flag as not at risk
2. If the row is a 1st level sub heading - leave no flag as not at risk
3. If the task isn't started by the start date = at risk!
4. If the project has started but the review date is in the past = at risk!
5. If there is no date all all in either columns - leave no flag as not at risk

MY SUM DOESN'T WORK RIGHT YET!

@Andrée Starå 🙏



(4) Master Task List - Smartsheet.com - Google Chrome_2.jpg

(4) Master Task List - Smartsheet.com - Google Chrome.jpg

James Keuning

First, use this to identify your level: =COUNT(ANCESTORS(ID@row)) -- ID is the field, change this to whatever.

Then I would create five checkbox columns:

1. Level > 1
2. Start Date NOT >= Today
3. Target Due date... you get the point here
4. review date passed
5. the no date test (I am not sure I understand this one)

Maybe I have some of these backwards, in which case, just use NOT to get it so your desirable outcome TRUE or FALSE is accurate.

Then test the columns with your IF statements.The way I do it is I create a column for each IF statement, so that I can make sure that it works, and I use 999 in the FALSE position. For example:

Column A: IF(Test1=TRUE,"True Result",999)

and then in Column B: IF(Test2=TRUE,"True Result",999)

And then if both of those work, I place the second statement in the "999" position of the first:

IF(Test1=TRUE,"True Result",IF(Test2=TRUE,"True Result",999))

Gavin Worsley

thank you very much @James Keuning with much perseverance last night I managed to arrive at a solution in the formula to avoid the extra columns.

Bassam Khalil

Hi @Gavin Worsley

Hope you are fine, i advise you to add a helper column call it "Level" and use the following formula to define the level of the task:

=IF(COUNT(CHILDREN(task@row)) > 0, COUNT(ANCESTORS()) + 1)

then you can use this level easily to check the at-risk status of your tasks

Gavin Worsley

thank you @Bassam Khalil you can't see it on the sheet in the image but I have managed to use this in the sheet to assist the formula. thank you

Andrée Starå

Hi @Gavin Worsley

I hope you're well and safe!

I didn't get a message that you mentioned me in your post.

Glad you got it working!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!