Less than won't work in my formula

Cynthia Wolf
Cynthia Wolf ✭✭
edited 03/09/24 in Formulas and Functions

Need some help I am trying to get a formula to return "Less" when the value is less then or equal to 0 and "More" when it greater then 0. The column I am comparing to is a formula to give me the totals however it appears to be returning a number based on how the column is formatted so it should be able to do this comparison however it will only show "Less" is the value is 0, anything less then 0 still returns a more. Appreciate any guidance you can give.

Here is the formula: =IF([FY 2022]@row > 0, "More", "Less")


Thank you, Cynthia


Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    edited 03/11/24 Answer ✓

    Hi @Cynthia Wolf

    0.4 is more than 0.

    Do you want anything less than 1 to be marked as "Less"?

    If so, change the 0 in your formula to 1. This would mean any negative numbers and anything between 0 and 1 (including 1.0) would be marked as "Less".

    =IF([FY 2022]@row > 1, "More", "Less")

    If you want 1.0 to be marked as "More", you can use a greater than and equal to.

    =IF([FY 2022]@row >= 1, "More", "Less")

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Both of the formula you shared look fine. Can you share a screen shot where you have a value less than 0 that is returning "more"? And share the formula in both of the cells in that situation.

  • Cynthia Wolf
    Cynthia Wolf ✭✭
    edited 03/11/24

    Thank you for looking at , you can see from here that the line where it is .4 it is showing more the attached have the formulas for both lines.


  • KPH
    KPH ✭✭✭✭✭✭
    edited 03/11/24 Answer ✓

    Hi @Cynthia Wolf

    0.4 is more than 0.

    Do you want anything less than 1 to be marked as "Less"?

    If so, change the 0 in your formula to 1. This would mean any negative numbers and anything between 0 and 1 (including 1.0) would be marked as "Less".

    =IF([FY 2022]@row > 1, "More", "Less")

    If you want 1.0 to be marked as "More", you can use a greater than and equal to.

    =IF([FY 2022]@row >= 1, "More", "Less")

  • Do you ever have a day / week where you are losing your mind? Thank you so much as yes it should be less then 1.

  • KPH
    KPH ✭✭✭✭✭✭

    😍 sometimes an extra eye is all it takes. Glad I could help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!