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

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…