If cell is empty or blank,

SBash

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

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

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

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, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SBash

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

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

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

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

Hi Nic,

 

Many Thanks

Best Regards,

saqib

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