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

Options

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%

• ✭✭✭✭✭✭
Options

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

bassam.khalil2009@gmail.com

• Options

thanks, i have sent you a sheet

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Options

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

• Options

=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"))))))

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Options

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

• Employee
Options

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.