# 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 %Completed-Time 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.

Cathy

Tags:

• ✭✭✭✭✭

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

• ✭✭✭✭✭

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:andree@workbold.com | 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:andree@workbold.com | 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

• ✭✭✭✭✭✭

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:andree@workbold.com | 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!