# If cell is empty or blank,

✭✭✭✭✭✭
edited 12/09/19

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Hi Nic,

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Hi Nic,

Many Thanks

Best Regards,

saqib

• ✭✭✭✭✭✭

Happy to help!

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.