Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Greater than, Less than or equal to formula help, also produce "" when cell is blank

    ✭✭
    in Formulas and Functions

    Can someone help me fix my formula;

    Goal output

    • Task Actual Duration is greater than Task Duration Days then "Longer"
    • Task Actual Duration is less than Task Duration Days then "Shorter"
    • Task Actual Duration = Task Duration Days then "Expected"
    • If Task Actual Duration is blank then " "

    Right now my formula doesn't account for the equal to component and doesn't account for if the "Task Actual Duration" is blank.

    =IF([Task Actual Duration]@row > [Task Duration Days]@row, "Longer", "Shorter")

    Thanks for helping me fix formula, my guess is it needs some nesting which i am not good at.

    Smartsheet Greater than.png


    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭✭
      Answer ✓

      @dgloballab 

      Please try the following formula:

      =IF(OR(ISBLANK([Task Duration Days]@row), ISBLANK([Task Actual Duration]@row)), "",
 IF([Task Actual Duration]@row > [Task Duration Days]@row, "Longer",
 IF([Task Actual Duration]@row = [Task Duration Days]@row, "Expected", "Shorter")))

      the following screenshot shows the result:

      image.png


      PMP Certified

      bassam.khalil2009@gmail.com

      ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭

      Hi @dgloballab

      Hope you are fine, please try the following formula:

      =IF(OR(ISBLANK([Task Duration Days]@row), ISBLANK([Task Actual Duration]@row)), "",
 IF([Task Actual Duration]@row >= [Task Duration Days]@row, "Longer", "Shorter"))

      the following screenshot shows the result:

      image.png


      PMP Certified

      bassam.khalil2009@gmail.com

      ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

      · Share on FacebookShare on Twitter
    • ✭✭

      Thank you @Bassam Khalil Any chance you can add in if they match that it should provide "Expected"?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      @dgloballab 

      did you mean

      [Task Actual Duration]@row = [Task Duration Days]@row then "Expected"


      PMP Certified

      bassam.khalil2009@gmail.com

      ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      Answer ✓

      @dgloballab 

      Please try the following formula:

      =IF(OR(ISBLANK([Task Duration Days]@row), ISBLANK([Task Actual Duration]@row)), "",
 IF([Task Actual Duration]@row > [Task Duration Days]@row, "Longer",
 IF([Task Actual Duration]@row = [Task Duration Days]@row, "Expected", "Shorter")))

      the following screenshot shows the result:

      image.png


      PMP Certified

      bassam.khalil2009@gmail.com

      ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

      · Share on FacebookShare on Twitter
    • ✭✭

      Thanks so much! works perfectly!

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      edited 11/03/21

      @dgloballab

      Excellent, i will be happy to help you any time.

      PMP Certified

      bassam.khalil2009@gmail.com

      ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions