Finding At Risk Calculation

Options

I am trying to find out why my sheet is marking tasks "at risk" and what the formula is. It seems to be random but I'm sure there is some rule or calculation somewhere that I'm not seeing. Can you help me find out where it might be?

Answers

  • dojones
    dojones ✭✭✭✭
    Options

    At Risk is also called Schedule at Risk and Schedule Health. Edit the column formulas to see the formula, but I'll post them below.

    At Risk =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")

    Within your sheet you will also see Schedule Delta% and Schedule Delta (Working Days)

    Schedule Delta% = =IFERROR([Schedule Delta (Working Days)]@row / Duration@row, "")

    Schedule Delta (Working Days) = IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) + 1, ""))), "")

    Basically it's comparing your Target End Date to the End Date, getting the number of NewWork days remaining and then dividing by Duration. Greater than 10% off is red.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!