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

Terence Garland

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%

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

Terence Garland

thanks, i have sent you a sheet

Genevieve P.

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

Terence Garland

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

Terence Garland

=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.

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?

Terence Garland

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.

https://community.smartsheet.com/discussion/comment/302728#Comment_302728

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.