Help with formula that has negative numbers

Cathy Fraser

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.

Thanks for your help

Cathy

Cathy Fraser

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

Cathy Fraser

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

Andrée Starå

Hi @Cathy Fraser

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!

Cathy Fraser

Hi Andree,

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

Andrée Starå

@Cathy Fraser

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!

Cathy Fraser

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

Andrée Starå

@Cathy Fraser

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!