If cell is empty or blank,

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

Find more posts tagged with

Sales

Finance

Formulas

Real Estate

Support

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.