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.

Thanks for your help

Cathy

Tags:

Best Answer

  • Cathy Fraser
    Cathy Fraser ✭✭✭✭✭
    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

  • Cathy Fraser
    Cathy Fraser ✭✭✭✭✭

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


  • Andrée Starå
    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!

    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.

  • Cathy Fraser
    Cathy Fraser ✭✭✭✭✭

    Hi Andree,

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

  • Andrée Starå
    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!

    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.

  • Cathy Fraser
    Cathy Fraser ✭✭✭✭✭
    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

  • Andrée Starå
    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!

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!