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

Options

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.


Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @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:


    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"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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:


    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"

  • dgloballab
    Options

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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @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"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @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:


    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"

  • dgloballab
    Options

    Thanks so much! works perfectly!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 11/03/21
    Options

    @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"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!