Formula to auto-summarise Delivery Risk assessment

SailingSheets

Hi all,

I'm attempting to build a function into my master milestone tracking sheet which will auto-populate my ‘Delivery Risk’ column, based on a series of conditions met in other cells in the row. Underpinning thinking is as follows:

• ‘Target Milestone Date’ are set for every row
• Assumption that ‘Actual Completion Date’ will be blank for each ‘Delivery Risk’ condition
• The ‘Progress’ column is manually updated by project leads based on, surprisingly, progress
• The only condition where this isn’t the case, is when an ‘Actual Completion Date’ is entered, which runs an automation to change ‘Progress’ to ‘Complete’
• Therefore, I’ve been trying to create a formula using =IF(AND(ISBLANK etc to address the logical conditions listed in the table below, but have been struggling with the complexity of the conditions
• ‘Status’ auto populates based on conditional formating

Any help or advice in how to set this formula up would be much appreciated.

Thanks,

Guy

Paul McGuinness

HI @SailingSheets

This formula should do the trick for you

=IF(AND([Target Milestone Date]@row - TODAY() > 14, Progress@row = "Not Started"), "On Track", IF(AND([Target Milestone Date]@row - TODAY() > 7, Progress@row = "In Progress"), "On Track", IF(AND([Target Milestone Date]@row - TODAY() < 14, [Target Milestone Date]@row - TODAY() > 6, Progress@row = "Not Started"), "At Risk", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = "In Progress"), "At Risk", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = "Not Started"), "Behind", IF(AND([Target Milestone Date]@row <= TODAY(), ISBLANK(Progress@row)), "Behind", IF(Progress@row = "Complete", "", "Error")))))))

Tested and working as a column formula

Hope that helps

Thanks

Paul

Paul McGuinness

HI @SailingSheets

This formula should do the trick for you

=IF(AND([Target Milestone Date]@row - TODAY() > 14, Progress@row = "Not Started"), "On Track", IF(AND([Target Milestone Date]@row - TODAY() > 7, Progress@row = "In Progress"), "On Track", IF(AND([Target Milestone Date]@row - TODAY() < 14, [Target Milestone Date]@row - TODAY() > 6, Progress@row = "Not Started"), "At Risk", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = "In Progress"), "At Risk", IF(AND([Target Milestone Date]@row - TODAY() < 7, Progress@row = "Not Started"), "Behind", IF(AND([Target Milestone Date]@row <= TODAY(), ISBLANK(Progress@row)), "Behind", IF(Progress@row = "Complete", "", "Error")))))))

Tested and working as a column formula

Hope that helps

Thanks

Paul

SailingSheets

Hi Paul,

Amazing, this looks to be working great. Thank you very much!

Guy