calculate difference between actual variance of networkdays in % vs manual input % to change robots

Hi, im trying to turn robots a certain color based on the above question only on a parent row

Current logic:

- Red: if past due OR variance greater than 20%

- Yellow: if variance between 0-20%

- Blue: if on track (also if > 0% but only starting at future date - for the error report/conditional format)

- Green: activity is in the past+100% OR activity today and marked at 100%

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Terence Garland 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    Or if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.


    My Email for sharing : Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • thanks, i have sent you a sheet

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Terence Garland

    Were you able to create a formula for your logic? If not, it would be helpful to see a screen capture of your sheet showing column names and the details you're looking to evaluate (but please block out any sensitive data).

    Cheers,

    Genevieve

  • Hi, im still trying, any help would be great, i can share the sheet if you like, just provide email address


  • =IF(START@row = "", "", IF(AND(START@row > TODAY(), [%]@row = 0), "", IF((AND(TODAY() > FINISH@row, IF(TODAY() = START@row, 0, IF(TODAY() > FINISH@row, 1, IF(AND(TODAY() > START@row, TODAY() <= FINISH@row), IF(COUNT(CHILDREN(t@row)) > 0, SUM(CHILDREN(duration@row), CHILDREN(t@row)), (NETWORKDAYS(START@row, FINISH@row) / duration@row * 100)), 0))) > [%]@row)), "Red", IF(OR(AND(TODAY() >= FINISH@row, [%]@row = 1), (IF(TODAY() = START@row, 0, IF(TODAY() > FINISH@row, 1, IF(AND(TODAY() > START@row, TODAY() <= FINISH@row), IF(COUNT(CHILDREN(t@row)) > 0, SUM(CHILDREN(duration@row), CHILDREN(t@row)), (NETWORKDAYS(START@row, FINISH@row) / duration@row * 100)), 0))) = 1)), "Green", IF(IF(TODAY() = START@row, 0, IF(TODAY() > FINISH@row, 1, IF(AND(TODAY() > START@row, TODAY() <= FINISH@row), IF(COUNT(CHILDREN(t@row)) > 0, SUM(CHILDREN(duration@row), CHILDREN(t@row)), (NETWORKDAYS(START@row, FINISH@row) / duration@row * 100)), 0))) <= [%]@row, "Blue", IF((IF(TODAY() = START@row, 0, IF(TODAY() > FINISH@row, 1, IF(AND(TODAY() > START@row, TODAY() <= FINISH@row), IF(COUNT(CHILDREN(t@row)) > 0, SUM(CHILDREN(duration@row), CHILDREN(t@row)), (NETWORKDAYS(START@row, FINISH@row) / duration@row * 100)), 0)))) - [%]@row <= 0.2, "Yellow", "Red"))))))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Terence Garland

    My apologies for the delay, but wow, that is a very epic formula! Does it work for all instances that you were looking for?

  • i think so, im still looking for an easy formula to calculate variance as a percent without having to add a networkdays duration column

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Terence Garland

    I'm sorry that I haven't been much help at all on this thread! At this point I would stick with your formula above, with the networkdays duration column; I personally can't think of another way to complete your goal but I may just be looking at it with a narrow point of view.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!