If cell is empty or blank,

SBash
SBash ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi, 

I have currently formulated the following formula,

=IF([Days (Difference Forecast - Baseline)]1 <= 5, "Green", IF(AND([Days (Difference Forecast - Baseline)]1 > 5, [Days (Difference Forecast - Baseline)]1 <= 10), "Amber", "Red"))

which is working efficiently, but problem occurs when the cell is empty, and it show me return "Green". As my formula is saying "Put green if the value is <=5". So it is taking an empty cell <  and showing green

How can is use ISBlank formula to put Empty cell or "-"

Or how can overcome with such situation

Many Thanks

Comments

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Just add it to the front of your formula as the initial check:

    =IF(ISBLANK([Days (Difference Forecast - Baseline)]@row, "", IF([Days (Difference Forecast - Baseline)]@row<= 5, "Green", IF(AND([Days (Difference Forecast - Baseline)]@row> 5, [Days (Difference Forecast - Baseline)]@row<= 10), "Amber", "Red"))

     

  • SBash
    SBash ✭✭✭✭✭✭

    Hi Nic, 

    I have tried your above mentioned instructions, but unfortunately the result is "incorrect Argument"

    for reference i have attached the screenshot

    Many Thanks

    Smartsheet.PNG

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Can you share the formula or sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • SBash
    SBash ✭✭✭✭✭✭
    edited 04/09/19

    Hi Andree

    Currently i have not fix data, i have taken some dummy data to test, i have made a template. the logic behind is to calculate the days as compare with Baseline Finish Date & Forecast Finish Date.

    Baseline Finish date = planned completion date 

    Forecast Actual Finish Date = Actual Completion date

    Days (Difference Forecast - Baseline) = the difference between both dates

    Conditional Logic of Project Delivery Status RAG:

    If the Forecast Actual Finish days is greater than or equal to Baseline Finish Date but less than 5 days, then set RAG to Green

    If the Forecast Actual Finish days is greater than 5 days to Baseline Finish Date but less than or equal to 10 days, then set RAG to Yellow

    If the Forecast Actual Finish days is greater than 10 days to Baseline Finish Date, then set RAG to Red

    Formula = IF([Days (Difference Forecast - Baseline)]1 <= 5, "Green", IF(AND([Days (Difference Forecast - Baseline)]1 > 5, [Days (Difference Forecast - Baseline)]1 <= 10), "Yellow", "Red"))

     

    It works fine but the problem arise , RAG shows Green even if the "Days (Difference Forecast - Baseline)" is blank. As i have given instruction in formula <=5, "Green"

    i need support, how can put the field empty or "-" if the difference row is blank

     

    many Thanks

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    I might have missed something on my last one. I've tested this one and it is working for me: 

    =IF(ISBLANK([Days (Difference Forecast - Baseline)]@row), "", IF([Days (Difference Forecast - Baseline)]@row <= 5, "Green", IF(AND([Days (Difference Forecast - Baseline)]@row > 5, [Days (Difference Forecast - Baseline)]@row <= 10), "Amber", "Red")))

  • SBash
    SBash ✭✭✭✭✭✭

    Hi Nic, 

    Many Thanks, Your formula works

    on the other hand i have also in parallel build this formula and its showing the same effect.

    =IF([Days (Difference Forecast - Baseline)]2 = "", "", IF([Days (Difference Forecast - Baseline)]2 <= 5, "Green", IF(AND([Days (Difference Forecast - Baseline)]2 > 5, [Days (Difference Forecast - Baseline)]2 <= 10), "Yellow", "Red")))

    Do you think, is there will be any difference in both? means any drawbacks

     

    Many Thanks

    Best Regards

    Saqib

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Your formula should be just fine. Although I'd replace the specific cell references with @row. It just makes it more efficient.  

    https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

  • SBash
    SBash ✭✭✭✭✭✭

    Hi Nic,

     

    Many Thanks

    Best Regards,

    saqib

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    I saw that Nic answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.