Help with formula that has negative numbers
Hi, I am trying to work out a formula that would return the below # value so I can then change status in a different column
0% to 25% = "1"
26% to 100% = "2"
1% to 25% = "3"
26% to 50% = "4"
51% to 100% = 5
this is the formula that I am trying to get to work, it only gets so far and then stops.
'=IF([% Completed Vs Time Lapsed% (H)]@row < 0.51, "5", IF([% Completed Vs Time Lapsed% (H)]@row > 0.51, "4", IF([% Completed Vs Time Lapsed% (H)]@row > 0.26, "3", IF([% Completed Vs Time Lapsed% (H)]@row < 0.99, "2", IF([% Completed Vs Time Lapsed% (H)]@row < 0.26, "1")))))
this is the column that is being used
The last column is the one I am trying to build the # values off %CompletedTime Lapsed% then they want to outline the below
= Result
0 to 25 On Track (Light Green) =1
26 to 100 Ahead of schedule (Richer Green) = 2
 1 to  25 Mildly Behind schedule (Yellow) = 3
26 to 50 Considerably Behind Schedule (Orange) = 4
 51 to 100 Schedule at Risk (Red) = 5
So I thought I would give each bracket a number value in a helper column and then drive the change in status from the number value
I hope that all make sense.
Thanks for your help
Cathy
Best Answer

Hi Andree,
Finally got the formula to work, so I thought I would just let you know what it ended up being.
=IFERROR(IF([% Completed Vs Time Lapsed% (H)]@row > 0.25, 2, IF([% Completed Vs Time Lapsed% (H)]@row > 0.01, 1, IF([% Completed Vs Time Lapsed% (H)]@row > 0.25, 3, IF([% Completed Vs Time Lapsed% (H)]@row > 0.51, 4, IF([% Completed Vs Time Lapsed% (H)]@row < 0.52, 5))))), "6")
Thanks so much for your help, with out I would not have gotten to the final solution.
Regards
Cathy
Answers

Sorry should have mentioned that the formula is based on my helper column

I hope you're well and safe!
Try something like this. (when it's a number, you shouldn't surround it with "" because Smartsheet reads that as text)
=IF([% Completed Vs Time Lapsed% (H)]@row < 0.51, 5, IF([% Completed Vs Time Lapsed% (H)]@row > 0.51, 4, IF([% Completed Vs Time Lapsed% (H)]@row > 0.26, 3, IF([% Completed Vs Time Lapsed% (H)]@row < 0.99, 2, IF([% Completed Vs Time Lapsed% (H)]@row < 0.26, 1)))))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Hi Andree,
Thanks for the formula, but it is still seeming to be stopping at 4.

Happy to help!
I missed reversing the order.
Try that, and it should work.
Did it work?
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Hi Andree,
Finally got the formula to work, so I thought I would just let you know what it ended up being.
=IFERROR(IF([% Completed Vs Time Lapsed% (H)]@row > 0.25, 2, IF([% Completed Vs Time Lapsed% (H)]@row > 0.01, 1, IF([% Completed Vs Time Lapsed% (H)]@row > 0.25, 3, IF([% Completed Vs Time Lapsed% (H)]@row > 0.51, 4, IF([% Completed Vs Time Lapsed% (H)]@row < 0.52, 5))))), "6")
Thanks so much for your help, with out I would not have gotten to the final solution.
Regards
Cathy

Glad we got it working!
Happy to help!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Check out the Formula Handbook template!